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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-236
MXCI Examples of UPDATE
NonStop SQL/MX updates both rows.
MXCI Examples of UPDATE
•
Update a single row of the ORDERS table that contains information about order
number 200300 and change the delivery date:
UPDATE sales.orders
SET deliv_date = DATE '1998-05-02'
WHERE ordernum = 200300;
•
Update several rows of the CUSTOMER table:
UPDATE sales.customer
SET credit = 'A1'
WHERE custnum IN (21, 3333, 324);
•
Update all rows of the CUSTOMER table to the default credit 'C1':
UPDATE sales.customer
SET credit = 'C1';
•
Update the salary of each employee working for all departments located in
Chicago:
UPDATE persnl.employee
SET salary = salary * 1.1
WHERE deptnum IN
(SELECT deptnum FROM persnl.dept
WHERE location = 'CHICAGO');
The subquery is evaluated for each row of the DEPT table and returns department
numbers for departments located in Chicago.
•
Suppose that you want to change the employee number of a manager of a
department. Because EMPNUM is a primary key of the EMPLOYEE table, you
must delete the employee's record and insert a record with the new number.
You must also update the DEPT table to change the MANAGER column to the
employee's new number. To ensure all your changes take place (or that none of
them do), perform the operation as a transaction:
SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
--- SQL operation complete.
BEGIN WORK;
--- SQL operation complete.
DELETE FROM persnl.employee
WHERE empnum = 23;
--- 1 row(s) deleted.
INSERT INTO persnl.employee
(empnum, first_name, last_name, deptnum, salary)