NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
D-39
Examples—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.
•
Locking
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.
•
Use in host programs
When using an SQL cursor in a 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 the following values to SQLCODE after a DELETE:
After a successful DELETE, the SQLCA contains the exact number of deleted rows.
Examples—DELETE
•
The following DELETE removes the row for JOHN WALKER from the
EMPLOYEE table:
DELETE FROM PERSNL.EMPLOYEE
WHERE FIRST_NAME = "JOHN" AND LAST_NAME = "WALKER";
•
The following 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;
•
The following 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