If you wish to UPDATE or DELETE a row in a host program, the DECLARE CURSOR must include FOR UPDATE. If columns are not stated, all columns can be updated. Syntax is:

     EXEC SQL
       DECLARE cursor_name [CURSOR FOR]
        SELECT column1, column2 ...
          FROM table_name ...
         WHERE ...
          [FOR UPDATE [OF column1, column2 ...]]
     END-EXEC

This SELECT FOR UPDATE can select only one table or view. DISTINCT, ORDER BY, and UNION can not be used. The DECLARE CURSOR FOR UPDATE would look like this:

     EXEC SQL
       DECLARE VENDOR_CSR
        SELECT VENDOR_NAME, STATUS
          FROM VENDOR_TBL
         WHERE COUNTRY = :SEARCH-COUNTRY
           FOR UPDATE OF STATUS
     END-EXEC

A column that is to be updated does not have to appear in the SELECT statement. The OPEN, FETCH, and CLOSE statements used for UPDATE or DELETE are identical to those used for retrieval. However, UPDATE or DELETE must have the phrase WHERE CURRENT OF. If WHERE CURRENT OF is not stated, the entire table will be updated or deleted.

     EXEC SQL
       UPDATE table_name
          SET column1 = value1,
              column2 = value2 ...
        WHERE CURRENT OF cursor_name
     END-EXEC

     EXEC SQL
       DELETE table_name
        WHERE CURRENT OF cursor_name
     END-EXEC

     EXEC SQL
       UPDATE VENDOR_TBL
          SET STATUS = :WS-STATUS
        WHERE CURRENT OF VENDOR_CSR
     END-EXEC