Putting It All Together

This help section showed how to use a cursor to update a table in the database. The following code:

    EXEC SQL
        DECLARE COBCUR1 CURSOR FOR 
        SELECT C_FIRST_NAME, C_LAST_NAME
            FROM CUSTOMER
            WHERE C_LAST_NAME = 'Snead'
        FOR UPDATE
    END-EXEC.

    EXEC SQL 
        OPEN COBCUR1 
    END-EXEC.

perform until SQLCODE not equal 0

    EXEC SQL
        FETCH COBCUR1
        INTO :C-FIRST-NAME, :C-LAST-NAME
    END-EXEC
    IF sqlcode EQUAL 0
          
        DISPLAY "Updating " C-FIRST-NAME , C-LAST-NAME 
        EXEC SQL
            UPDATE CUSTOMER SET C_INFO = 'Revised' 
            WHERE CURRENT OF COBCUR1
        END-EXEC
    end-if
end-perform.

EXEC SQL 
    CLOSE COBCUR1 
END-EXEC.

instructs the program to perform these steps:

1. Declare a cursor to SELECT the values of C_FIRST_NAME and C_LAST_NAME from the customer table in those rows where C_LAST_NAME equals Snead. These rows will be updated.
2. Open the cursor.
3. Fetch the values and insert them into the host variables :C_FIRST_NAME and :C_LAST_NAME.
4. If the program doesn't encounter an error condition, display a message indicating that the fields are being updated.
5. Change the value of the C_INFO field to Revised for those rows that meet the condition set in the cursor (C_LAST_NAME equals Snead).
6. Close the cursor.

The figure in Updating Data – update.sqb showed the output of the update program. If you want, run the select3 program to see the contents of the updated fields.