SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-12
Multirow Delete Operation Using a Single Statement
statement deletes only one row of the EMPLOYEE table because each value in
EMPNUM is unique. EMPNUM is the primary key of the EMPLOYEE table.
{ User enters a value for :hostvar_key to }
{ specify the employee to delete. }
...
EXEC SQL
DELETE FROM =employee
WHERE empnum = :hostvar_key;
Multirow Delete Operation Using a Single Statement
Multirow operations (sometimes called set operations) are performed on one or more
rows by a single SQL statement. Set operations provide maximum efficiency in both
coding and execution. Use set operations wherever possible for deleting sets of rows.
However, sometimes, you must check a row before deleting it. In this case, you must
use cursors as descibed under Multirow Delete Operation Using a Cursor on
page 3-13.
This DELETE statement deletes all rows from the EMPLOYEE table that contain
information on employees working in department 100:
EXEC SQL
DELETE FROM =employee
WHERE deptnum = 100;
This DELETE statement deletes all rows that contain a department number matching
the DEPTNUM value that is assigned to the host variable :HOSTVAR-DEPT. (For
example, all employees working in department 100.) DEPTNUM is not a primary key;
therefore, more than one row for each value of DEPTNUM can exist.
{ User enters a value for :hostvar_dept }
{ to specify the employee to delete. }
...
EXEC SQL
DELETE FROM =employee
WHERE deptnum = :hostvar_dept;
This DELETE statement deletes from the ORDERS table all orders that were placed
with sale representative 568 by any customer except the customer whose number is
3210:
EXEC SQL
DELETE FROM sales.orders
WHERE salesrep = 568 AND custnum <> 3210;
This DELETE statement deletes from the PARTSUPP table all suppliers who charge
more than $1600.00 for terminals. Terminals have part numbers in the range of 6400 to
6700.
EXEC SQL
DELETE FROM invent.partsupp