SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-40
Multirow Update Operation Using a Cursor
The following statement updates several rows of the CUSTOMER table to revise each
customer’s credit rating to A1.
EXEC SQL
UPDATE SALES.CUSTOMER SET CREDIT = "A1"
WHERE CUSTNUM IN (21, 3333, 324);
In the next example, the salary of each employee working for a department that is
located in San Francisco is increased by 10 percent. The subquery is evaluated for
each row of the DEPT table and returns department numbers for departments located
in San Francisco.
EXEC SQL
UPDATE EMPLOYEE SET SALARY = SALARY * 1.1
WHERE DEPTNUM IN (SELECT DEPTNUM FROM DEPT
WHERE LOCATION = "SAN FRANCISCO");
Multirow Update Operation Using a Cursor
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.
When you use a cursor to select rows for subsequent update, you must use the FOR
UPDATE OF clause in the cursor declaration. If you determine you want to update the
data, you use a WHERE CURRENT OF clause to update the row from the table.
For audited tables and views, a TMF transaction must be in progress. The same TMF
transaction must include the OPEN, FETCH, and UPDATE operations.
The following example declares a cursor, fetches and tests the data, then updates the
DEPTNUM column at the current cursor position:
EXEC SQL
DECLARE CHECK_EMP CURSOR FOR
SELECT DEPTNUM
FROM EMPLOYEE
WHERE DEPTNUM = :DEPTNUM_DEL
Caution. Do not use a stand-alone DELETE operation to delete a row that has been retrieved
using a cursor FETCH operation. Doing so can validate the cursor’s buffering for the table and
might degrade performance.