SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-231
Considerations for UPDATE
The default access option is the isolation level of the containing transaction, which
is determined according to the rules specified in Isolation Level on page 10-45.
WHERE CURRENT OF {cursor-name | ext-cursor-name}
specifies the name of a cursor (or extended cursor) positioned at the row to
update. If you specify cursor-name for an audited table or view, the UPDATE
must execute within a transaction that also includes the FETCH for the row. Each
column to be updated must appear in the FOR UPDATE clause of the cursor
declaration.
For more information on searched and positioned UPDATE statements in embedded
SQL programs, see the SQL/MX Programming Manual for C and COBOL.
Considerations for UPDATE
In a searched UPDATE, rows are updated in sequence. If an error occurs and you are
not using DP2’s Savepoint feature, NonStop SQL/MX returns an error message and
stops updating the table. NonStop SQL/MX automatically rolls back the transaction to
undo the updated data in the audited table.
If the default INSERT_VSBB is set to USER, NonStop SQL/MX does not use
statement atomicity. Unless you are updating only a few records, you should not
disable INSERT_VSBB to use statement atomicity, because performance is affected.
Perform UPDATE STATISTICS on the tables so that row estimates are correct.
To see what rollback mode NonStop SQL/MX is choosing, you can prepare the query,
and then perform DISPLAY_OPTIONS:
display_explain options 'f' my_query;
Token “x” means that the transaction will be rolled back. Token “s” means that
NonStop SQL/MX will choose DP2 savepoints. See DISPLAY_EXPLAIN Command on
page 4-13 for details. For details about these defaults, see INSERT_VSBB on
page 10-61 and UPD_SAVEPOINT_ON_ERROR
on page 10-64.
Authorization Requirements
UPDATE requires authority to read and write to the table or view being updated and
authority to read any table or view specified in subqueries used in the search condition.
A column of a view can be updated if its underlying column in the base table can be
updated.
Transaction Initiation and Termination
The UPDATE statement automatically initiates a transaction if there is no active
transaction and the statement references an audited table. Otherwise, you can
explicitly initiate a transaction with the BEGIN WORK statement. When a transaction is
started, the SQL statements execute within that transaction until a COMMIT or
ROLLBACK is encountered or an error occurs.
C/COBOL