Updating table with values of prior records

From: Robert Wachtel <rwachtel_at_gmx.de>
Date: Mon, 27 Feb 2006 23:11:29 +0100
Message-ID: <46hc0lFb9v91U1@individual.net>

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; Received on Mon Feb 27 2006 - 16:11:29 CST