Updating a Table

Use an UPDATE statement to modify the contents of one or more rows. All rows in a table that satisfy the search condition are updated in accordance with the assignments in the SET clause. You can update only one table in a single statement. Following is an example:


 Problem

Initialize new records (those with STATE equal to WS-STATE) with
year-to-date sales equal to the host variable WS-YTD-SALES, and the
salesman ID equal to the host variable WS-SLMN-ID.
 Solution
               .
               .
       (COBOL statements)
               .
               .
  1      EXEC SQL
  2          DECLARE CUSTUPD CURSOR FOR
  3              SELECT *
  4                 FROM CUSTOMERS
  5                 WHERE STATE = :WS-STATE
  6      END-EXEC.
  7      EXEC SQL WHENEVER NOT FOUND GOTO PROGEND END-EXEC.
  8      EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC.
  9      EXEC SQL WHENEVER SQLERROR GOTO ERRORTN END-EXEC.
                   .
                   .
 10      EXEC SQL OPEN CUSTUPD END-EXEC.
 11      PERFORM PROCESS-CUSTOMERS-LOOP THROUGH
 12          END-PROCESS-CUSTOMERS-LOOP.
 13      EXEC SQL CLOSE CUSTUPD END-EXEC.
                   .
                   .
 14   PROCESS-CUSTOMERS-LOOP.
 15      EXEC SQL
 16          FETCH CUSTUPD INTO
 17               :WC-IND-CD,
 18               :WC-CUSTNO,
 19               :WC-NAME,
 20               :WC-ADDR-1,
 21               :WC-ADDR-2,
 22               :WC-CITY,
 23               :WC-STATE,
 24               :WC-ZIP,
 25               :WC-CRED-IND,
 26               :WC-AREA-CD,
 27               :WC-PH-EXCH,
 28               :WC-PH-NO,
 29               :WC-OPEN-DOL,
 30               :WC-YTD-SALES,
 31               :WC-ACT-YR,
 32               :WC-ACT-MO,
 33               :WC-ACT-DAY,
 34               :WC-SLMN-ID
 35      END-EXEC.
 36      IF SQLCODE = ZERO
 37          EXEC SQL
 38              UPDATE CUSTOMERS
 39                  SET YTD_SALES = :WS-YTD-SALES,
 40                      SLMN_ID = :WS-SLMN-ID
 41              WHERE CURRENT OF CUSTUPD
 42          END-EXEC.
               .
               .
 43   END-PROCESS-CUSTOMERS-LOOP.

Line 5

The search condition in the SELECT of the DECLARE CURSOR statement specifies that the temporary result table contains rows only where the value of the STATE column is equal to the value of the host variable WS-STATE.

Line 7

The exception condition, NOT FOUND, directs the program to go to a program end routine if an SQL return code of +100 is received.

Line 8

The exception condition, SQLWARNING, directs the program to continue execution if a warning condition or a positive SQL return code other than +100 is received.

Line 9

The exception condition, SQLERROR, directs the program to go to an error handling routine in the host program if a negative SQL return code is received.

Line 10

The cursor named in the DECLARE CURSOR statement is opened and positioned before the first row of the result table formed by the SELECT in the DECLARE CURSOR statement.

Lines 11-12

The processing loop is performed. This loop contains the FETCH statement to position the cursor.

Line 13

The cursor named in the DECLARE CURSOR statement is closed.

Lines 16-34

The FETCH statement positions the cursor on the first (or next) row of the temporary result table and places that row into the host variables listed in this statement.

Lines 38-41

The value of each named column is updated in the row where the cursor is currently positioned.