SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-15
DELETE
This DELETE statement deletes from the PARTSUPP table all suppliers who charge
more than the host variable :HOSTVAR^MAX^COST for terminals. Terminals have part
numbers in the range of the host variables :HOSTVAR^MIN to :HOSTVAR^MAX.
EXEC SQL
DELETE FROM invent.partsupp
WHERE partnum BETWEEN :hostvar^min AND :hostvar^max
AND partcost > hostvar^max^cost ; .
Using a Cursor to Delete Rows
To read and test a value before you delete a row, you must use a cursor. A cursor
allows you to delete 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 delete the row depending on your
test logic. If you determine that you want to delete the data from the table, you use a
DELETE WHERE CURRENT OF statement.
For audited tables and views, a TMF transaction must be in progress. The same TMF
transaction must include both fetching and deleting the row. If you want to ensure that
a lock is held on the row you are deleting until the transaction completes, you can
declare the cursor with a DELETE WHERE CURRENT OF statement that specifies a
column in the rows to be deleted.
This example declares a cursor, fetches and tests the data, and then deletes the row:
EXEC SQL
DECLARE check_emp CURSOR FOR
SELECT deptnum
FROM employee
WHERE deptnum = :deptnum^del
OR deptnum = :deptnum^upd
FOR UPDATE OF deptnum ;
EXEC SQL
OPEN check_emp ;
EXEC SQL
FETCH check_emp
INTO :deptnum ;
! Program logic to test the data
...
EXEC SQL
DELETE FROM employee ! Delete the current row
WHERE CURRENT OF check_emp ;
EXEC SQL
CLOSE check_emp ;
Note. Do not use a stand-alone DELETE operation to delete a row that has been retrieved
using a cursor FETCH operation. This can invalidate the cursor's buffering for the table and
might degrade performance.