SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
U-6
Examples—UPDATE
Examples—UPDATE

This example updates a single row of the ORDERS table that contains information
about order number 200038 and changes the delivery date:
>> UPDATE SALES.ORDERS SET DELIV_DATE = 880522
+> WHERE ORDERNUM = 200038;
--- 1 row(s) updated.

This example updates several rows of the CUSTOMER table:
>> UPDATE SALES.CUSTOMER SET CREDIT = "A1"
+> WHERE CUSTNUM IN (21, 3333, 324);
--- 3 row(s) updated.

This example increases the salary of each employee working for a department
located in San Francisco. The subquery is evaluated for each row of the DEPT
table and returns department numbers for departments located in San Francisco.
>> VOLUME $VOL1.PERSNL;
>> UPDATE EMPLOYEE SET SALARY = SALARY * 1.1
+> WHERE DEPTNUM IN (SELECT DEPTNUM FROM DEPT
+> WHERE LOCATION = "SAN FRANCISCO" );

Suppose that you want to change an employee's number. The employee is the
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 that all your changes take place (or that none
of them do), you should perform the operation as a TMF transaction, as shown:
>> VOLUME $VOL.PERSNL;
>> BEGIN WORK;
>> DELETE FROM EMPLOYEE WHERE EMPNUM = 23;
--- 1 row(s) deleted.
>> INSERT INTO EMPLOYEE
+> VALUES (50, "JERRY", "HOWARD", 1000, 100, 137000.10);
--- 1 row(s) inserted.
>> UPDATE DEPT SET MANAGER = 50 WHERE DEPTNUM = 1000;
--- 1 row(s) updated.
>> COMMIT WORK;