SQL Statements › UPDATE › Positioned UPDATE › Example 3

Example 3

This example assumes the system for assigning manager numbers has been modified due to changes at the division level. To locate the manager numbers which must be updated, this example uses a SELECT in a DECLARE CURSOR statement. When the result table is obtained and the cursor is positioned, an UPDATE statement, using the WHERE CURRENT OF clause, updates the value for MGRNO.

 EXEC SQL
      DECLARE C1 CURSOR FOR
           SELECT DEPTNO, DEPTNAME, MGRNO
           FROM DEPTTBL
           WHERE ADMDEPT = 'A0'
 END-EXEC
EXEC SQL
      OPEN C1
 END-EXEC
FETCH-LOOP.
    IF SQLCODE = 0
        EXEC SQL
            FETCH C1 INTO :DNUM, :DNAME, :MNUM
        END EXEC.
        EXEC SQL
            UPDATE DEPTTBL
                SET MGRNO = MGRNO + 100000
                WHERE CURRENT OF C1
        END-EXEC.
        GO TO FETCH LOOP.
EXEC SQL
      CLOSE C1
 END-EXEC