UPDATE (Positioned)

Syntax:

>>---EXEC SQL---.-----------------.--.------------.----->
                +--FOR :host_integer-+  +-AT db_name-+  

                                  +------ ,-------+
                                  V               |
 >---UPDATE---table_name-----SET--column_expression----->

 >--WHERE CURRENT OF--cursor_name---END-EXEC---><

Parameters:

:`host_integer`	A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
AT `db_name`	The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement.
`table_name`	The table to be updated.
`column_expression`	A value for a particular column name. This value can be an expression or a null value.
`cursor_name`	A previously declared, opened, and fetched cursor.

Comments:

Do not use the FOR clause if the UPDATE is part of a DECLARE CURSOR statement.

After execution, SQLERRD(3) contains the number of elements processed. For UPDATE it is the total number of rows updated.

ODBC supports positioned update, which updates the row most recently fetched by using a cursor, in the Extended Syntax (it was in the core Syntax for ODBC 1.0 but was moved to the Extended Syntax for ODBC 2.0). Not all drivers provide support for positioned update, although OpenESQL sets ODBC cursor names to be the same as COBOL cursor names to facilitate positioned update and delete.

With some ODBC drivers, the select statement used by the cursor must contain a FOR UPDATE clause to enable positioned update.

The other form of UPDATE used in standard SQL statements is known as a searched update.

You cannot use host arrays with positioned update.

Most data sources require specific combinations of SCROLLOPTION and CONCURRENCY to be specified either by SET statements or in the DECLARE CURSOR statement.

The ODBC cursor library provides a restricted implementation of positioned update which can be enabled by compiling with SQL(USECURLIB=YES) and using SCROLLOPTION STATIC and CONCURRENCY OPTCCVAL (or OPTIMISTIC).

Example:

     EXEC SQL CONNECT TO 'srv1' USER 'sa' END-EXEC

     EXEC SQL DECLARE C1 CURSOR FOR
        SELECT last_name, first_name
        FROM staff
        FOR UPDATE
     END-EXEC     
    
     EXEC SQL
        OPEN C1
     END-EXEC

     PERFORM UNTIL SQLCODE NOT = ZERO

        EXEC SQL
            FETCH C1 INTO :fname,:lname
        END-EXEC

        IF SQLCODE = ZERO
           DISPLAY fname " " lname
           DISPLAY "Update?" 
           ACCEPT reply
           IF reply = "y"
              DISPLAY "New last name?" 
              ACCEPT lname
              EXEC SQL
                 UPDATE staff 
                 SET last_name=:lname WHERE CURRENT OF c1
              END-EXEC
              DISPLAY "update sqlcode=" SQLCODE
            END-IF
        END-IF
     END-PERFORM

     EXEC SQL DISCONNECT ALL END-EXEC
     STOP RUN.