SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-44
UPDATE
EXEC SQL END DECLARE SECTION;
...
! Procedure code:
PROC update^orders;
BEGIN ...
newdate ':=' "1987-05-22";
orders.ordernum := 200038;
EXEC SQL
UPDATE orders
SET deliv^date = :newdate TYPE AS DATE
WHERE ordernum = :orders.ordernum
STABLE ACCESS;
END;
...
Updating Multiple Rows Using a Single UPDATE Statement
Multi-row 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 described under Using a Cursor to Update Multiple Rows on page 3-44.
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. (A user enters values for
:HOSTVAR^INC and :HOSTVAR^MIN^SALARY.)
EXEC SQL
UPDATE employee
SET salary = salary * :hostvar^inc
WHERE salary < :hostvar^min^salary ;
This example updates all rows in the DEPTNUM column that contain the value in the
:HOSTVAR^OLD^DEPTNUM host variable. Effectively, all employees who were
previously in department 100 are now in department 200. (A user enters values for
:HOSTVAR^OLD^DEPTNUM and :HOSTVAR^NEW^DEPTNUM.)
EXEC SQL
UPDATE employee
SET deptnum = :hostvar^new^deptnum
WHERE deptnum = :hostvar^old^deptnum ;
Using a Cursor to Update Multiple Rows
To read and test each value before you update it, you must use a cursor. A cursor
allows you to update a set of rows one row at a time. You specify the set of rows with
the SELECT clause in the DECLARE CURSOR statement. You read each row using a
FETCH statement, test the data, and then ignore or update the row depending on your
test logic.