Using Cursors to Update Data
To use a cursor to update data, specify the FOR UPDATE clause in the cursor's declaration:
EXEC SQL DECLARE cursor_name CURSOR FOR
select_statement
FOR UPDATE OF column {, column};
The FOR UPDATE clause must list any columns in the selected database table that may require updating. Columns that have not been declared cannot be updated. If you are deleting rows, you do not need to declare the cursor for update.
The syntaxes for the CLOSE and FETCH statements are no different for cursors opened for update. However, the UPDATE statement has an extended version for cursors:
EXEC SQL UPDATE tablename
SET column = expression {, column = expression}
WHERE CURRENT OF cursor_name;
The WHERE clause of the cursor version specifies the row to which the cursor currently points, and the update affects only data in that row. Each column specified in the SET clause must have been declared for updating in the DECLARE CURSOR statement.
Be sure that the cursor is pointing to a row (a fetch has been executed) before performing a cursor update. The UPDATE statement does not advance the cursor. A fetch is still required to move the cursor forward one row. Two cursor updates not separated by a fetch will cause the same row to be updated twice or generate an error on the second update, depending on the underlying DBMS.