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;