| Robert Wachtel 2006-02-28, 8:25 pm |
| Hi!
Given is a table SEASONS with a DATE field and an ID field. Some IDs have
values, some IDs are NULL values. Now I want to update the NULL values with
the last assigned ID value of a prior DATE.
STARTDATE SEASON_ID
---------------- ------------
01.01.2006 00:00 1
02.01.2006 00:00 (null)
03.01.2006 00:00 2
04.01.2006 00:00 (null)
My below mentioned UPDATE statement fails with an ORA-00904 "S"."STARTDATE"
invalid identifier error. It seems that the table to be updated is out of
focus in the subquery.
Any help greatly appreciated.
tia
Robert
DROP TABLE SEASONS;
CREATE TABLE SEASONS (
"STARTDATE" DATE NOT NULL,
"SEASON_ID" NUMBER(15,5) NULL );
INSERT INTO SEASONS VALUES (TO_DATE('01.01.2006', 'DD.MM.YYYY'), 1);
INSERT INTO SEASONS VALUES (TO_DATE('02.01.2006', 'DD.MM.YYYY'), NULL);
INSERT INTO SEASONS VALUES (TO_DATE('03.01.2006', 'DD.MM.YYYY'), 2);
INSERT INTO SEASONS VALUES (TO_DATE('04.01.2006', 'DD.MM.YYYY'), NULL);
SELECT * FROM SEASONS;
UPDATE SEASONS S
SET S.SEASON_ID =
(SELECT SEASON_ID FROM
(SELECT * FROM SEASONS
WHERE SEASON_ID IS NOT NULL
AND STARTDATE < S.STARTDATE
ORDER BY STARTDATE DESC)
WHERE ROWNUM = 1)
WHERE S.SEASON_ID IS NULL;
SELECT * FROM SEASONS;
|