Updating data as it is retrieved from a table

You can update rows of data as you retrieve them by using a cursor.

On the select-statement, use FOR UPDATE OF followed by a list of columns that may be updated. Then use the cursor-controlled UPDATE statement. The WHERE CURRENT OF clause names the cursor that points to the row you want to update. If a FOR UPDATE OF, an ORDER BY, a FOR READ ONLY, or a SCROLL clause without the DYNAMIC clause is not specified, all columns can be updated.

If a multiple-row FETCH statement has been specified and run, the cursor is positioned on the last row of the block. Therefore, if the WHERE CURRENT OF clause is specified on the UPDATE statement, the last row in the block is updated. If a row within the block must be updated, the program must first position the cursor on that row. Then the UPDATE WHERE CURRENT OF can be specified. Consider the following example:

Table 1. Updating a table
Scrollable Cursor SQL Statement	Comments
EXEC SQL DECLARE THISEMP DYNAMIC SCROLL CURSOR FOR SELECT EMPNO, WORKDEPT, BONUS FROM CORPDATA.EMPLOYEE WHERE WORKDEPT = 'D11' FOR UPDATE OF BONUS END-EXEC.
EXEC SQL OPEN THISEMP END-EXEC.
EXEC SQL WHENEVER NOT FOUND GO TO CLOSE-THISEMP END-EXEC.
EXEC SQL FETCH NEXT FROM THISEMP FOR 5 ROWS INTO :DEPTINFO :IND-ARRAY END-EXEC.	DEPTINFO and IND-ARRAY are declared in the program as a host structure array and an indicator array.
... determine if any employees in department D11 receive a bonus less than $500.00. If so, update that record to the new minimum of $500.00.
EXEC SQL FETCH RELATIVE :NUMBACK FROM THISEMP END-EXEC.	... positions to the record in the block to update by fetching in the reverse order.
EXEC SQL UPDATE CORPDATA.EMPLOYEE SET BONUS = 500 WHERE CURRENT OF THISEMP END-EXEC.	... updates the bonus for the employee in department D11 that is under the new $500.00 minimum.
EXEC SQL FETCH RELATIVE :NUMBACK FROM THISEMP FOR 5 ROWS INTO :DEPTINFO :IND-ARRAY END-EXEC.	... positions to the beginning of the same block that was already fetched and fetches the block again. (NUMBACK -(5 - NUMBACK - 1))
... branch back to determine if any more employees in the block have a bonus under $500.00. ... branch back to fetch and process the next block of rows.
CLOSE-THISEMP. EXEC SQL CLOSE THISEMP END-EXEC.