SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-14
DELETE
Executing a Single-Row DELETE Statement
To delete a single row, you move a key value to a host variable and then use the host
variable in the WHERE clause of the DELETE statement. The following DELETE
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. A user
enter a value for the :HOSTVAR^KEY host variable.
EXEC SQL
DELETE FROM =employee
WHERE empnum = :hostvar^key ;
Executing a Multi-Row DELETE Statement
Multi-row 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 described under Using a Cursor to Delete Rows on page 3-15.
In these examples, a user enters the values for all host variables.
The following DELETE statement deletes all rows from the EMPLOYEE table that
contain information for employees in the department identified by the host variable
:HOSTVAR^DEPTNUM.
EXEC SQL
DELETE FROM =employee
WHERE deptnum = hostvar^deptnum ;
The following 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
EXEC SQL
DELETE FROM =employee
WHERE deptnum = :hostvar^dept ;
The following DELETE statement deletes from the ORDERS table all orders that were
placed with the sales representative identified by the host variable :HOSTVAR^REP by
any customer except the customer whose number is identified by the host variable
:HOSTVAR^CUSTNUM.
EXEC SQL
DELETE FROM sales.orders
WHERE salesrep = hostvar^rep
AND custnum <> hostvar^custnum;