DB2 UDB for iSeries SQL: Updating data as it is retrieved from a table

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.

Parent topic:
Changing data in a table using the UPDATE statement

Related reference

Using a cursor

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.