NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
D-38
Considerations—DELETE
DELETE does not delete a table or protection view, even if you delete the last row in the
table or view. Use DROP to delete a table or view.
name
is the name of the table or protection view (or an equivalent DEFINE) from which
to delete rows.
The file organization of the table or underlying table must be key-sequenced or
relative. You cannot use DELETE to delete rows from an entry-sequenced table,
from a catalog table, or from a shorthand view.
WHERE search-cond
is a search condition that specifies criteria for selecting rows to delete. The search
condition cannot include a subquery that refers to name or its underlying table.
If you omit both the WHERE and WHERE CURRENT clauses, SQL deletes all
rows.
[FOR] {STABLE } ACCESS
{REPEATABLE}
determines the locking for the rows to delete.
The default is FOR STABLE ACCESS. See Access Options
on page A-1 for more
information.
WHERE CURRENT OF cursor-name
(for use in programs only) specifies the name of a cursor that is positioned at the
row to delete. If cursor-name points to an audited table or view, the DELETE
must execute within a TMF transaction that also includes the FETCH for the row.
You cannot specify WHERE CURRENT OF when using parallel execution.
Considerations—DELETE
•
Authorization requirements
DELETE FROM name
[[ WHERE search-cond ] [ [FOR] {STABLE } ACCESS ]]
[ [ {REPEATABLE} ]]
[ ]
[ WHERE CURRENT OF cursor-name ]
STABLE locks all data accessed but releases locks on unmodified rows
without waiting for the end of the transaction.
REPEATABLE locks all data accessed until the end of the transaction.