I am using DB2 9.5 UDB on windows

I want to update multiple rows in a single sql statement.

I have a table which has an id,date start, date end, sequence.
The last row has a date end of 9999-12-31 and date start 01-01-2008
the row before has a null date end and date start 01-01-2007
the date end of this row needs setting to the previous rows date start minus 1 day (31-12-2007)

I have created this query which gets me the rows with the correct dates
and sequence
SELECT A.id, A.SEQUENCE, A.DATE_start, DATE(B.DATE_start) - 1 DAY AS DATE_end1, B.SEQUENCE, B.DATE_start
FROM HOLD A, HOLD B
WHERE A.id = B.id
AND A.DATE_end IS NULL
AND A.SEQUENCE = ( B.SEQUENCE -1)

This returns
ID SEQUENCE DATE_start DATE_end1 SEQUENCE DATE_start
--------------- ----------- -------------- ------------ ----- ------------
200000 5 2004-07-12 31/07/2004 6 2004-08-01
200000 6 2004-08-01 29/07/2007 7 2007-07-30
200000 7 2007-07-30 14/10/2007 8 2007-10-15

i need to update row 5,6,7 with the date end1 value.

I have tried various methods - i am trying to do this with sql only

UPDATE HOLD
SET DATE_end =
( SELECT DATE(B.DATE_start) - 1 DAY
FROM HOLD A, HOLD B
WHERE A.id = B.id
AND A.DATE_end IS NULL
AND A.SEQUENCE = ( B.SEQUENCE -1))

this returns a -811.

I have tried other where statements but this does not work.

Advice whould be greatly appreciated.

You have to correlate the subselect to the table being updated, something like:

Disclaimer: not tested.

That worked as i needed it to

Looks like I was trying to be to clever with the sql i was writing

It's really not at all confusing if you think logically, the way SQL does:
- what do I want to update? Table HOLD; I'll call it "a", because it's shorter;
- what rows? Those where DATE_END is NULL ;
- what column? DATE_END;
- what do I want to set it to? Something from the table HOLD; since it will be a different set of rows I'll call it "b" to avoid confusing it with "a";
- now, for each matching row in "a", what row in "b" do I need? That with the same value of ID and next in SEQUENCE.
- what happens if there is no record with the same ID and next in sequence? Nothing; an empty set will be returned, and DATE_END will be set to NULL, that is, no change.