SQL & PL/SQL

11R2/Linux

Hello,

I have a table with 2 records per serial_number.
First record (older start_date) has to be updated as the end_date (currently 99991231) must be next record's (same serial_number) start_date -1.

CREATE TABLE test_3
  (
     SERIAL_NUMBER  VARCHAR2(100) NOT NULL,    
     START_DATE            NUMBER,
     END_DATE              NUMBER
    
  )
  


 select SERIAL_NUMBER, START_DATE,END_DATE FROM test_3 order by start_date;
  
SERIAL_NUMBER        START_DATE   END_DATE
-------------------- ---------- ----------
001ADE809710           20070101   99991231----> should be 20110915
001ADE809710           20110916   99991231
------
-----
-----



My attempt is:

update test_3 t3 set
      t3.end_date = (select t2.start_date -1 
                 from test_3 t2, test_3 t3
                 where t2.serial_number = t3.serial_number
                   and t2.end_date = 99991231
                   and t2.start_date > t3.start_date) 
                   where  (select t2.start_date 
                 from test_3 t2, test_3 t3
                 where t2.serial_number = t3.serial_number
                   and t2.end_date = 99991231
                   and t2.start_date < t3.start_date
)


Error at Command Line:12 Column:2
Error report:
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
                 
----------------------

Any subscript works fine by itself.

What I'm missing here?

Thx,