Back in week 49/2010 I wrote about how to perform a SELECT combined with an INSERT. This option makes it possible for you to retrieve the values of columns with default values. In DB2 version 9 SELECT has been extended further. It is now possible to combine SELECT with UPDATE and SELECT with DELETE.

In a SELECT with UPDATE you can choose between returning the updated rows as they were before the update:

SELECT keyCol, col3
FROM OLD TABLE
( UPDATE myTable SET col3 = col3 * 0.02
  WHERE keyCol >= :keyColValue
)

Or as they become after the update:

SELECT keyCol, col3
FROM FINAL TABLE
( UPDATE myTable SET col3 = col3 * 0.02
  WHERE keyCol >= :keyColValue
)

In both examples the returned values i KeyCol will remain the same as this column is not updated. The first example will return the original values of col3 while the following example will return the new values of col3. If the last example had contained an update of a TIMESTAMP column where the new value is set to the value of CURRENT TIMESTAMP you were able to retrieve the actual value of CURRENT TIMESTAMP.

For a SELECT with DELETE there is of course only one option, namely retrieval of the rows as they look before they are deleted:

SELECT keyCol, col3
FRoM oLD TABLE
( DELETE FRoM myTable
  WHERE keyCol >= :keyColValue
)

SELECT with DELETE is a very good option if you want to archive deleted data somewhere else than in the DB2 log. One option is to transfer the data to another DB2 table. Your needs may be different for archiving deleted data, but now you have a range of new possibilities.