Update a table and return both the old and new values

Question

Im writing a VB app that is scrubbing some data inside a DB2 database. In a few tables i want to update entire columns. For example an account number column. I am changing all account numbers to start at 1, and increment as I go down the list. Id like to be able to return both the old account number, and the new one so I can generate some kind of report I can reference so I dont lose the original values. Im updating columns as so:

DECLARE @accntnum INT 
SET @accntnum = 0 
UPDATE accounts
SET @accntnum = accntnum = @accntnum + 1
GO 

Is there a way for me to return both the original accntnum and the new one in one table?

Answer 1

DB2 has a really nifty feature where you can select data from a "data change statement". This was tested on DB2 for Linux/Unix/Windows, but I think that it should also work on at least DB2 for z/OS.

For your numbering, you might considering creating a sequence, as well. Then your update would be something like:

 CREATE SEQUENCE acct_seq
     START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO CYCLE
     CACHE 24
;

SELECT accntnum  AS new_acct, old_acct
FROM FINAL TABLE (
    UPDATE accounts INCLUDE(old_acct INT)
    SET accntnum = NEXT VALUE FOR acct_seq, old_acct = accntnum
)
ORDER BY old_acct;

The INCLUDE part creates a new column in the resulting table with the name and the data type specified, and then you can set the value in the update statement as you would any other field.