Neoview SQL Reference Manual (R2.4)

(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 '2007-10-02',
TIMESTAMP '2007-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.
Examples of Self-Referencing Inserts
• This is an example of a self-referencing insert:
insert into table1 select pk+?, b, c from table1
• This is an example of a self-referencing insert where the target of the insert, table1, is also
used in a subquery of the insert-source:
insert into table1
select a+16, b, c from table2 where table2.b not in
(select b from table1 where a > 16)
• This is an example of a self-referencing UPDATE statement, where the table being updated
is scanned in a subquery:
update table3 set b = b + 2000 where a, b =
(select a, b from table3 where b > 200
• This is an example of a self-referencing DELETE statement, where the table from which
rows are deleted is scanned in a subquery:
delete from table1 where a in
(select a from table1 where b > 200)
146 SQL Statements