• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag
 

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Correlated SQL statement
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Correlated SQL statement


  • Subject: Correlated SQL statement
  • From: Florijan Stamenkovic <email@hidden>
  • Date: Fri, 9 Mar 2007 22:39:45 +0100

Hi all,

This is a bit off topic, but I can't subscribe to the OpenBase list, trying to fix that. In the meanwhile, I have a problem...

I am new to writing SQL, so sorry if this is a dumb question, I am just trying to get my head around it. I have a problem writing a correlated statement. My situation is this:

There is a table which is related to itself in a one to many type relationship, where PARENT_ID is used as a foreign key. So, it becomes a tree structure. This table also contains a UNIQUE_NUMBER field, which should contain a number that is unique but relative to the parent item. So that every item in the tree structure has a unique path. As in (the item number being the desired UNIQUE_NUMBER value):

1. Item
	1. Item
	2. Item
	3. Item
		1. Item
		2. Item (path is 1.3.2)
	4. Item
2. Item
3. Item
...


I am trying to make an SQL update statement which should generate those numbers for all items which do not have them yet. The table where this is happening is called STRUCTURE_ITEM. The SQL I came up with is:


UPDATE
STRUCTURE_ITEM str1
SET
UNIQUE_NUMBER =
(
	SELECT
	max(UNIQUE_NUMBER)
	FROM STRUCTURE_ITEM str2
	WHERE
	str1.STRUCTURE_ITEM_ID = str2.PARENT_ITEM_ID
	GROUP BY
	PARENT_ITEM_ID
)
WHERE
str1.UNIQUE_NUMBER = NULL

But it keeps giving me this error: SQL ERROR - [position 0, near '(null)'] COMMA expected but not found in GROUP BY.

If I isolate the UPDATE statement and use a fixed value instead of the correlated statement, it works. If I isolate the correlated SELECT statement, add ", STRUCTURE_ITEM str1" to the WHERE clause, it works also. Combined it does not work. Can anyone please point to what I am doing wrong?

Thanks,
Florijan
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden


  • Prev by Date: Re: WebObjects 5: Updating time zone data
  • Next by Date: Re: Getting information on updated n:m relationship
  • Previous by thread: Re: DBUnit in Eclipse config question
  • Next by thread: locations of DST information
  • Index(es):
    • Date
    • Thread