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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-235
Considerations for UPDATE
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.
Although NonStop SQL/MX cannot successfully update all rows and return the
requested results, it does not return an error. Instead, only one row is updated, and the
results for this single updated row are returned in the set of host variables.
If you execute the same SELECT UPDATE statement in MXCI NonStop SQL/MX
returns these results:
>>select * from
+>(update =TAB1
+>set v3 = 'DCBA'
+>WHERE k1 ='0001'
+>SKIP CONFLICT ACCESS)
+>AS PIPO
+>READ UNCOMMITTED ACCESS
+>;
K1 K2 V3
---- ---- ------------------------------------------------------
0001 AAAA DCBA
0001 BBBB DCBA
--- 2 row(s) selected.
>>