SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-237
Considerations for UPDATE
Command on page 4-13. For information about these defaults, see INSERT_VSBB on
page 10-59 and UPD_SAVEPOINT_ON_ERROR on page 10-62.
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.
Positioned UPDATE With AUTOCOMMIT
If you are using the positioned form of UPDATE, check that AUTOCOMMIT is set to
OFF before you open a cursor or initialize the SQLJ iterator. Otherwise, NonStop
SQL/MX commits the transaction after each UPDATE statement and closes the cursor
or the iterator. Consequently, you might get rows fetched by your cursor or iterator that
are part of different transactions.
Isolation Levels of Transactions and Access Options of
Statements
The isolation level of an SQL/MX transaction defines the degree to which the
operations on data within that transaction are affected by operations of concurrent
transactions. When you specify access options for the DML statements within a
transaction, you override the isolation level of the containing transaction. Each
statement then executes with its individual access option.
You can explicitly set the isolation level of a transaction with the SET TRANSACTION
statement. See SET TRANSACTION Statement on page 2-223. The default isolation
level of a transaction is determined according to the rules specified in Isolation Level
on page 10-44.
It is important to note that the SET TRANSACTION statement might cause a dynamic
recompilation of the DML statements within the next transaction. Dynamic
recompilation occurs if NonStop SQL/MX detects a change in the transaction mode at
run time compared with the transaction mode at the time of static SQL compilation. To
avoid dynamic recompilation because of a change in the transaction mode, consider
Note. NonStop SQL/MX accepts SQL/MP keywords as synonyms for READ UNCOMMITTED,
STABLE, and SERIALIZABLE.
Embed
Embed