SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
D-40
Considerations—DELETE
Considerations—DELETE

DELETE requires authority to read and write to the table or view being deleted and
to any table or view in a subquery of the search condition.

Rows must be locked to be deleted. The locking used depends on the access
option you specify in the WHERE clause or the access option you specify in the
SELECT portion of the associated DECLARE CURSOR statement.
If the deletion occurs through a search condition, the access option you specify in
a subquery determines the duration of locks applied to data in the tables and views
referred to in the subquery.
The default access option is STABLE.

When using an SQL cursor in a host program, a DELETE WHERE CURRENT
generally provides a performance benefit over a stand-alone DELETE. SQL uses
virtual sequential block buffering (VSBB) for updates through a cursor unless you
use another cursor or a stand-alone DELETE or UPDATE for the same table within
the same program. (Using a stand-alone DELETE or another cursor to access the
table within the same process, either directly or through a view, invalidates VSBB
and can degrade performance substantially.)
SQL returns these values to SQLCODE after a DELETE:
After a successful DELETE, the SQLCA contains the exact number of deleted
rows.
Examples—DELETE

This example on DELETE removes the row for JOHN WALKER from the
EMPLOYEE table:
DELETE FROM PERSNL.EMPLOYEE
WHERE FIRST_NAME = "JOHN" AND LAST_NAME = "WALKER";

This example on DELETE removes from the table ORDERS any orders placed
with sales representative 568 by any customer except customer number 3210:
DELETE FROM SALES.ORDERS
WHERE SALESREP = 568 AND CUSTNUM <> 3210;

This example on DELETE removes from the table PARTSUPP all suppliers who
charge more than $1600.00 for items that have part numbers in the range 6400 to
6700. The DELETE uses REPEATABLE access (which provides maximum
0 Successful DELETE
100 No rows found for a search condition
> 0 Warning code number
< 0 Error code number