>>---EXEC SQL---.-----------------.--.------------.-----> +--FOR :host_integer-+ +-AT db_name-+ +------ ,-------+ V | >---UPDATE---table_name-----SET--column_expression-----> >--WHERE CURRENT OF--cursor_name---END-EXEC---><
|: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.|
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).
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.