NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
U-4
Considerations—UPDATE
The default is STABLE.
See Access Options
on page A-1 for a detailed discussion of both STABLE and
REPEATABLE.
WHERE CURRENT OF cursor
(for use in programs only) specifies a host-program cursor to identify the row to be
updated. The cursor must be positioned at a single row to be updated and not
between rows.
For static SQL programs, each column to be updated must appear in the FOR
UPDATE clause of the cursor declaration. For dynamic SQL programs, each column
to be updated must appear in the FOR UPDATE clause of the SELECT that defines
the cursor.
You cannot specify WHERE CURRENT OF cursor when using parallel
execution.
Considerations—UPDATE
•
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 of the search condition.
You cannot use UPDATE to update a catalog table.
•
Locking
Rows must be locked to be updated. The condition that identifies the row to be
updated determines the locking protocol for the update. If a cursor identifies the row,
the ACCESS option of DECLARE CURSOR determines the locking protocol; if a
WHERE clause identifies the row, the access option in the UPDATE statement
determines the locking protocol.
•
Criteria for data in row
Each updated row must satisfy the assertions of the table or underlying table of the
view. An updated row from a protection view created with the WITH CHECK
OPTION must satisfy the view selection criteria. (The selection criteria are specified
in the WHERE clause of the AS select-statement clause in the CREATE
VIEW statement.) No column updates occur unless all of these conditions are
satisfied.
•
Reporting of updates
Rows are updated in sequence. If an error occurs, SQL returns an error message and
stops updating the table or view.
When the UPDATE finishes successfully, SQL reports the number of times rows
were updated during the operation.
Under certain conditions, updating a table with indexes causes SQL to update the
same row more than once, causing the number of updates that SQL reports to be