SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
U-4
Considerations—UPDATE
The default is REPEATABLE.
For a detailed discussion of both STABLE and REPEATABLE, see Access Options
on page A-1.
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

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.

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.

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 these conditions
are satisfied.

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
higher than the actual number of rows changed. The data in the table is correct
and the message correctly reports the number of times rows were updated. This
behavior (a variation of the “Halloween problem” described in computer science
literature) occurs when all these are true: