Correlated SQL statement
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