SQL/MP Programming Manual for COBOL85

Data Retrieval and Modification

HP NonStop SQL/MP Programming Manual for COBOL85—429326-004

4-20

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 program’s PAID 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.

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.