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-230
MXCI Examples of INSERT
select_list ............ %(10)
........
>>execute s1;
--- 2 row(s) inserted.
>>select * from test1;
COL1 COL2
----------- ----
1 100
2 200
11 100
12 200
--- 4 row(s) selected.
control query default BLOCK_TO_PREVENT_HALLOWEEN 'ON';
>>control query default BLOCK_TO_PREVENT_HALLOWEEN 'ON';
--- SQL operation complete.

This self-referencing INSERT statement uses the Blocking Plan Operator Method.
Look for the flag self_referencing_update and forced_sort in the explain
output. The forced sort causes the plan to block until all records are processed:
>>fc pre
>>prepare s1 from insert into test1 select col1 + 10, col2
from test1 where col1 <= 3;
..
--- SQL command prepared.
>>fc exp
>>explain s1;
..
-------------------------------------------------------------
----- PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... S1
PLAN_ID .................. 212204692084904912
ROWS_OUT ................ 33
EST_TOTAL_COST ........... 0.04
STATEMENT ................ insert into test1
select col1 + 10, col2
from test1
where col1 <= 3;
-------------------------------------------------------------
----- NODE LISTING
ROOT ====================================== SEQ_NO 7
ONLY CHILD 6
REQUESTS_IN .............. 1