Update a table with the 'nest record. from the same table

[Welshbird]

Hi guys - I need to come up with a query that will update a table using a value from the next record within the same table.

The table data looks like this:

CompID Record Date NextDate
1 a 1-Mar-03
2 a 1-Apr-03
3 b 1-May-03

and I need to be able to insert the value of 1-apr-03 into the 'nextdate' field on the first record.

This needs to happen whenever the field 'Record' is equal to itself and when the CompID = CompID + 1.

I thought I knew how to do this (have done it in MS-SQL before) so I tried to use almost the same code which looks like this:

update Table
set NextDate = 
		(select Table_1.Date NextDate)
		FROM         
		Table 
		INNER JOIN
    Table Table_1 
    ON 
    Table.Record = Table_1.Record 
    AND Table.CompID = Table_1.CompID - 1
		where
		(Table_1.CompID > 1);

This comes up with an error telling me that I dont have a 'from' where one is expected.

Would really appreciate some help with this!

Thanks

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[lewisp]

Try posting this on the Oracle 8i or 9i forums where your query will be received by a wider audience.

 

[egyhamada]

try this and it will work

update Table
set NextDate =
(select Table1.Date
FROM Table1
where Table1.CompID = Table.CompID + 1)
where Table.CompID < (biggest value for Comp id )