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