SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-323
MXCI Examples of UPDATE
statement_index ........ 0
olt_optimization ....... not used
affinity_value 1,932,384,263
est_memory_per_cpu .... 56
upd_action_on_error .... savepoint
xn_autoabort_interval -1
plan_version ....... 3,200
self_referencing_update forced_sort
MXCI_PROCESS ........... ON
SHOWCONTROL_UNEXTERNALI OFF
BLOCK_TO_PREVENT_HALLOW ON
select_list ............ %('500'), execution_count
..
>> execute s1;
--- 1 row(s) updated.
>>select * from test1;
COL1 COL2
----------- ----
1 100
2 500
--- 2 row(s) selected.

drop table test1;
create table test1(col1 int not null , col2 char(3),primary
key(col1) );
insert into test1 values ( 1, '100'), (2, '200');
--- 2 row(s) inserted.
prepare s1 from update test1 set col1 = 300 where col1 =
(select col1 from test1 where col1 > 1);
--- SQL command prepared.
>>explain s1;
-------------------------------------------------------------
----- PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... S1
PLAN_ID .................. 212204692566543846
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.06
STATEMENT ................ update test1
set col1 = 300
where col1 = (select col1 from
test1 where col1 >
1);