Updating previously retrieved data

To scroll backward through data and update it, use a scrollable cursor that is declared with the FOR UPDATE clause.

About this task

If a cursor uses FETCH statements to retrieve columns that will be updated later, specify FOR UPDATE OF when you select the columns. Then specify WHERE CURRENT OF in the subsequent UPDATE or DELETE statements. These clauses prevent Db2 from selecting access through an index on the columns that are being updated, which might otherwise cause Db2 to read the same row more than once.

Procedure

To update previously retrieved data, use these steps:

  1. Declare the cursor with the SENSITIVE STATIC SCROLL keywords.
  2. Open the cursor.
  3. Issue a FETCH statement to position the cursor at the end of the result table.
  4. Issue FETCH statements to move the cursor backward to the row that you want to update.
  5. Specify the WHERE CURRENT OF clause in the UPDATE or DELETE statement that updates the current row.
  6. Repeat steps 4 and 5 until all required rows are updated.
  7. When you have retrieved and updated all the data, close the cursor.