SQL/MP Programming Manual for COBOL

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
4-20
UPDATE Statement
* Process row values returned to host variables.
...
EXEC SQL CLOSE GET-NAME-ADDRESS END-EXEC.
UPDATE Statement
When used with a cursor, an UPDATE statement updates rows, one row at a time, in a
table or protection view. To identify the set of rows to update (or test), specify the FOR
UPDATE OF clause in the associated SELECT statement. Before you update each
row, you can test one or more column values. If you decide to update the row, specify
the WHERE CURRENT OF clause in the UPDATE statement.
To execute an UPDATE statement, a process started by the program must have read
and write access to the table or view being updated. It must also have read access to
tables or views specified in subqueries of the search condition. For information about
process access, see Required Access Authority on page 7-1.
Do not use a stand-alone UPDATE statement to update a row that has been retrieved
using a FETCH statement. A stand-alone UPDATE statement invalidates the cursor's
buffering for the table and can substantially degrade performance.
An UPDATE statement must be within the scope of all other SQL statements, including
the DECLARE CURSOR, OPEN, FETCH, INSERT, and CLOSE statements, that refer
to the cursor. For audited tables and views, the UPDATE statement must execute
within the same TMF transaction as the OPEN and FETCH statements for the cursor.
Example 4-3 on page 4-21 uses the GET-BY-PARTNUM cursor and the host variables
named NEW-PARTDESC, NEW-PRICE, and NEW-QTY to update the PARTS table.
This example also uses a cursor. Suppose that you want a cursor to position in the
PARTS table on the part number specified by host variable STARTING-PARTNUM, so
that the program can fetch rows and determine whether to update data in the columns.
The row updated is at the current position of the cursor GET-BY-PARTNUM. The
example declares the host variables NEW-PARTDESC, NEW-PRICE, and NEW-QTY
and sets them to the new values for the columns before executing the UPDATE
statement.