SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-39
Multirow Update Operation Using a Single
Statement
The following example changes an employee’s number. The employee is the manager
of a department. Because EMPNUM is a primary key of the EMPLOYEE table, the
code must delete the employee’s row and insert a row with the new number. The code
must also update the DEPT table to change the MANAGER column to the employee's
new number. To make sure that all of the changes take place (or that none of them do),
the operation is coded within a TMF transaction.
{ Procedure Code }
EXEC SQL BEGIN WORK;
EXEC SQL
DELETE FROM EMPLOYEE
WHERE EMPNUM = 23;
EXEC SQL
INSERT INTO EMPLOYEE
VALUES (50, "JERRY", "HOWARD", 1000, 100, 137000.10);
EXEC SQL
UPDATE DEPT SET MANAGER = 50
WHERE DEPTNUM = 1000;
EXEC SQL COMMIT WORK;
Multirow Update Operation Using a Single Statement
Multirow operations (sometimes called set operations) are performed on one or more
rows by a single SQL statement. These operations provide maximum efficiency in both
coding and execution. Use set operations wherever possible for updating sets of rows.
However, sometimes, you must check a row before updating it. In this case, you must
use cursors, as descibed under Cursor Declaration Examples on page 3-10
The following single statement updates the SALARY column of every row in the
EMPLOYEE table where the current value of the SALARY column is less than the
value of the host variable :HOSTVAR_MIN_SALARY.
EXEC SQL
UPDATE EMPLOYEE
SET SALARY = SALARY * :HOSTVAR_INC
WHERE SALARY < :HOSTVAR_MIN_SALARY;
The following example updates all the rows that currently contain the value 100 in the
DEPTNUM column. Effectively, all employees who were previously associated with
department 100 are now associated with department 200:
EXEC SQL
UPDATE EMPLOYEE
SET DEPTNUM = 200
WHERE DEPTNUM = 100;