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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-154
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.