Summary -
In this topic, we described about the below sections -
The cursor for the update is used to perform updates to the columns in the retrieved row(s).
Positioned UPDATE is allowed in the cursor for the update.
FOR UPDATE OF clause used to declare the updatable cursors.
FOR UPDATE OF followed with a list of columns that might get updated.
WHERE CURRENT OF used to update or delete the rows in updatable cursors using the current row positioning.
All columns can be updated if a FOR UPDATE OF, an ORDER BY, a FOR READ ONLY, or a SCROLL clause without a DYNAMIC clause is not specified.
If the "FOR UPDATE OF" clause is specified while declaring a cursor, the UPDATE statement might execute on any specific row or all rows of the result table. However, it is not mandatory to execute UPDATE for every FETCH.
Syntax -
Declaring Cursor - EXEC SQL DECLARE cursor-name/cursor-variable-name CURSOR FOR select-statement FOR UPDATE OF columns-list END-EXEC. Opening Cursor - EXEC SQL OPEN cursor-name/cursor-variable-name; END-EXEC. Fetching Cursor - EXEC SQL FETCH cursor-name/cursor-variable-name INTO :hv1, :hv2, . . :hvn; END-EXEC. UPDATE Statement - EXEC SQL UPDATE table_name SET column1 = :hv1, column2 = :hv2, . . columnn = :hvn WHERE CURRENT OF cursor-name/cursor-variable-name END-EXEC. Closing Cursor - EXEC SQL CLOSE cursor-name/cursor-variable-name; END-EXEC.
cursor-name -
- Cursor-name is the name of the cursor used in an application program to refer to the cursor.
- The cursor-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
cursor-variable-name -
- Cursor-variable-name is a working-storage variable that contains the cursor name used in an application program to refer to the cursor.
- The cursor-variable-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
Examples -
Input-

Scenario1 - Declare the cursor for updating column manager_id of employee_details table.
Code - DECLARE CURSOR - EXEC SQL DECLARE E1 CURSOR FOR SELECT EMP_NAME, MANAGER_ID FROM EMPLOYEE_DETAILS FOR UPDATE OF MANAGER_ID END-EXEC. UPDATE - EXEC SQL UPDATE EMPLOYEE_DETAILS SET MANAGER_ID = :WS-MANAGER-ID WHERE CURRENT OF E1 END-EXEC.
Scenario2 - Declare the cursor for updating all columns of the employee_details table.
Code - DECLARE CURSOR - EXEC SQL DECLARE E2 CURSOR FOR SELECT EMP_ID, EMP_NAME, DESIGNATION, MANAGER_ID, DATE_OF_HIRE, SALARY, DEPT_ID FROM EMPLOYEE_DETAILS FOR UPDATE END-EXEC. UPDATE - EXEC SQL UPDATE EMPLOYEE_DETAILS SET DESIGNATION = :WS-DESG, MANAGER-ID = :WS-MANAGER-ID, DATE_OF_HIRE = :WS-DOH, SALARY = :WS-SALARY WHERE CURRENT OF E2 END-EXEC.
Scenario3 - Declare the cursor for updating designation "FRESHER" in department 1000 of employee_details table.
Code - DECLARE CURSOR - EXEC SQL DECLARE E3 CURSOR FOR SELECT EMP_ID, EMP_NAME, DESIGNATION FROM EMPLOYEE_DETAILS WHERE DESIGNATION = "FRESHER" AND DEPT_ID = 1000 FOR UPDATE OF DESIGNATION END-EXEC. UPDATE - EXEC SQL UPDATE EMPLOYEE_DETAILS SET DESIGNATION = "JUNIOR DEVELOPER" WHERE CURRENT OF E3 END-EXEC.
Scenario4 - Declare the cursor for updating designation "FRESHER" in departments 1000 and 2000 of the employee_details table.
Code - DECLARE CURSOR - EXEC SQL DECLARE E4 CURSOR FOR SELECT EMP_ID, EMP_NAME, DESIGNATION FROM EMPLOYEE_DETAILS WHERE DESIGNATION = "FRESHER" AND DEPT_ID IN (1000, 2000) ORDER BY DEPT_ID ASC FOR UPDATE OF DESIGNATION END-EXEC. UPDATE - EXEC SQL UPDATE EMPLOYEE_DETAILS SET DESIGNATION = "JUNIOR DEVELOPER" WHERE CURRENT OF E4 END-EXEC.
Practical Example -
Scenario - The below example describes how the update cursor is coded in the COBOL + DB2 program.
Requirement - Hike the salary of TESTER(s) by 3000.
Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- ***************************** Top of Data ****************************** IDENTIFICATION DIVISION. PROGRAM-ID. CUPDATE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL INCLUDE EMPLDET END-EXEC. * DECLARING UPDATE CURSOR EXEC SQL DECLARE CSR1 CURSOR FOR SELECT EMP_ID, EMP_NAME, DESIGNATION, MANAGER_ID, SALARY FROM EMPLOYEE_DETAILS WHERE DESIGNATION = 'TESTER' AND DEPT_ID = 2000 FOR UPDATE OF SALARY END-EXEC. 01 WS-VAR. 05 WS-UCNT PIC 9(02) VALUE ZEROES. 05 WS-UPDATED-SAL PIC S9(5)V9(2) USAGE COMP-3. PROCEDURE DIVISION. * OPENING CURSOR EXEC SQL OPEN CSR1 END-EXEC. * FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE IF SQLCODE EQUAL ZERO PERFORM FETCH-EMP-DETAILS THRU FETCH-EMP-DETAILS-EXIT UNTIL SQLCODE NOT EQUAL 0 END-IF. * CLOSING CURSOR EXEC SQL CLOSE CSR1 END-EXEC. DISPLAY "NO OF ROWS UPDATED: ", WS-UCNT. STOP RUN. FETCH-EMP-DETAILS. * FETCH CURSOR EXEC SQL FETCH CSR1 INTO :EMP-ID, :EMP-NAME, :DESIGNATION, :MANAGER-ID :MANAGER-ID-NI, :SALARY END-EXEC. EVALUATE SQLCODE WHEN ZERO PERFORM UPDATE-EMP-DETAILS THRU UPDATE-EMP-DETAILS-EXIT WHEN 100 CONTINUE WHEN OTHER DISPLAY "DB2 ERROR: ", SQLCODE END-EVALUATE. FETCH-EMP-DETAILS-EXIT. EXIT. UPDATE-EMP-DETAILS. COMPUTE WS-UPDATED-SAL = SALARY + 3000. EXEC SQL UPDATE EMPLOYEE_DETAILS SET SALARY = :WS-UPDATED-SAL WHERE CURRENT OF CSR1 END-EXEC. EVALUATE SQLCODE WHEN ZERO COMPUTE WS-UCNT = WS-UCNT + 1 WHEN 100 DISPLAY "UPDATING ROW NOT FOUND" WHEN OTHER DISPLAY "DB2 ERROR: ", SQLCODE END-EVALUATE. UPDATE-EMP-DETAILS-EXIT. EXIT. **************************** Bottom of Data ****************************
Output -

employee_details table after UPDATE -
