DB2 - update increment based on timestamp

Question

After a complex operation (some database merge-ing) I have a table that needs to be updated based on timestamp.

JobsTable

Id  Time_stamp   Resource  RunNumber   
121      1           A         1 

122      2           A         1

123      3           B         1

124      4           B         1

125      5           A         2

The point is to Update the RunNumber column incrementally for each resource based on timestamp. So in the end the expected result is:

Id  Time_stamp   Resource  RunNumber   
121      1           A         1 

122      2           A         2  //changed

123      3           B         1

124      4           B         2  //changed 

125      5           A         3  //changed

I tried doing this in multiple ways. Since DB2 update does not support Join or With statements I tried something like:

update JOBSTABLE JT
SET RunNumber = 
(SELECT RunNumber
FROM (Select ID, ROW_NUMBER() OVER (ORDER BY TIME_STAMP ) RunNumber from JobsTable, ORDER BY TIME_STAMP) AS AAA
WHERE AAA.ID = JT.ID)
WHERE ID = ?

Error:

Assignment of a NULL value to a NOT NULL column "TBSPACEID=6, TABLEID=16, COLNO=2" is not allowed.. SQLCODE=-407, SQLSTATE=23502, DRIVER=3.64.82 SQL Code: -407, SQL State: 23502

Is this even possible? (I am aiming at doing this operation in a single query rather than using Cursors, etc..)

Thank you

mustaccio mustaccio · Accepted Answer · 2013-09-13T14:24:21

Firstly, your subselect has a syntax error, which tells me it's not the exact statement that you are trying to run. The error message is pretty clear -- in your actual statement the subselect sometimes returns NULL.

Secondly, you should probably be numbering rows within a partition by resource.

Thirdly, you could probably do with a single subselect anyway -- this is based on the statement you published:

update JOBSTABLE JT
SET RunNumber = 
(SELECT ROW_NUMBER() OVER (partition by resource ORDER BY TIME_STAMP ) 
 from JobsTable where id = JT.ID)

DB2 - update increment based on timestamp

1 Answers