Update query question

[heidi88]

Hi,

I need to update some records.

[BEFORE UPDATE]

CustNum OrderId startdt enddt InitOrderId
123 1 01/01/00 01/01/01
123 2 01/01/01 01/01/02
123 3 01/01/02 01/01/03

123 4 09/01/03 09/01/04
123 5 09/01/04 09/01/05
123 6 09/01/05 09/01/06
....

[AFTER UPDATE]

CustNum OrderId startdt enddt InitOrderId
123 1 01/01/00 01/01/01 NULL
123 2 01/01/01 01/01/02 1
123 3 01/01/02 01/01/03 1

123 4 09/01/03 09/01/04 NULL
123 5 09/01/04 09/01/05 5
123 6 09/01/05 09/01/06 6
...

I prefer not to use cursor. If anyone has any idea on how to update the initorderid field using set-based statement, that will be great.

Thanks a lot.

 

[heidi88]

Sorry, type error:

[BEFORE UPDATE]

CustNum OrderId startdt enddt InitOrderId
123 1 01/01/00 01/01/01
123 2 01/01/01 01/01/02
123 3 01/01/02 01/01/03

123 4 09/01/03 09/01/04
123 5 09/01/04 09/01/05
123 6 09/01/05 09/01/06
....

[AFTER UPDATE]

CustNum OrderId startdt enddt InitOrderId
123 1 01/01/00 01/01/01 NULL
123 2 01/01/01 01/01/02 1
123 3 01/01/02 01/01/03 1

123 4 09/01/03 09/01/04 NULL
123 5 09/01/04 09/01/05 4
123 6 09/01/05 09/01/06 4

 

[dbomrrsm]

what criteria is used to update the InitOrderId column ?

[blue]DBomrrsm[/blue]

 

[heidi88]

Trying to link continous records back to the original one. If 1st startdt= 2nd enddt, 2nd startdt = 3 enddt...mark the initorderid to the very first record.

Heidi