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-228
MXCI Examples of INSERT
(200,'PRODUCTION SUPV'),
(250,'ASSEMBLER'),
(300,'SALESREP'),
(400,'SYSTEM ANALYST'),
(420,'ENGINEER'),
(450,'PROGRAMMER'),
(500,'ACCOUNTANT'),
(600,'ADMINISTRATOR'),
(900,'SECRETARY');
--- 10 row(s) inserted.

The PROJECT table consists of five columns using the data types NUMERIC,
VARCHAR, DATE, TIMESTAMP, and INTERVAL. Insert values by using these
types:
INSERT INTO persnl.project
VALUES (1000, 'SALT LAKE CITY', DATE '1996-10-02',
TIMESTAMP '1996-12-21:08:15:00.00', INTERVAL '30' DAY);
--- 1 row(s) inserted.

Suppose that CUSTLIST is a view of all columns of the CUSTOMER table except
the credit rating. Insert information from the SUPPLIER table into the CUSTOMER
table through the CUSTLIST view, and then update the credit rating:
INSERT INTO sales.custlist
(SELECT * FROM invent.supplier
WHERE suppnum = 10);
UPDATE sales.customer
SET credit = 'A4'
WHERE custnum = 10;
You could use this sequence in the following situation. Suppose that one of your
suppliers has become a customer. If you use the same number for both the
customer and supplier numbers, you can select the information from the
SUPPLIER table for the new customer and insert it into the CUSTOMER table
through the CUSTLIST view (as shown in the example).
This operation works because the columns of the SUPPLIER table contain values
that correspond to the columns of the CUSTLIST view. Further, the credit rating
column in the CUSTOMER table is specified with a default value. If you want a
credit rating that is different from the default, you must update this column in the
row of new customer data.

This self-referencing INSERT statement uses the DP2 Locks Method. Look for the
flag self_referencing_update in the explain output:
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 insert into test1 select col1 + 10, col2 from