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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-240
Considerations for UPDATE
•
If an UPDATE operation is applied to a set of rows and an error is generated while
executing the UPDATE operation, and the transaction is rolled back, the actions of
the SET ON ROLLBACK clause apply only to the rows that were processed by the
UPDATE operation before the error was generated.
SET ON ROLLBACK Restrictions
The table must be audited. The columns used in the SET ON ROLLBACK clause:
•
Must be declared as NOT NULL.
•
Cannot be part of a referential integrity constraint or be part of a secondary index.
•
Cannot use the VARCHAR data type.
•
Cannot be used in the primary key, clustering key, or partitioning key.
Embedded SELECT UPDATE Behavior
When you use a SELECT UPDATE statement to perform a searched UPDATE in an
embedded statement, and more than one row satisfies the selection criteria, UPDATE
might give unexpected results.
Suppose you have a table with two rows:
>>select * from =TAB1;
K1 K2 V3
---- ---- ------------------------------------------------------
0001 AAAA
0001 BBBB
If you perform this statement:
EXEC SQL
SELECT *
INTO :hv_k1
, :hv_k2
, :hv_v3
FROM
(UPDATE =TAB1
SET v3 = 'ABCD'
WHERE k1 ='0001'
SKIP CONFLICT ACCESS
)
AS PIPO
READ UNCOMMITTED ACCESS
;
Both rows would satisfy the selection criteria, so both rows could be updated. However,
in this case it would be impossible to return the result, because NonStop SQL/MX can
only return the values for one row from a statement like this.
Pub/Sub