Description

FROM table-name or view-name
Identifies the table or view from which rows are to be deleted. The name must identify a table or view that exists at the current server, but it must not identify a catalog table, a view of a catalog table, or a view that is not deletable. For an explanation of deletable views, see CREATE VIEW.
correlation-clause
Can be used within the search-condition to designate the table or view and column names of the table or view. For an explanation of correlation-clause, see Queries. For an explanation of correlation-name, see Correlation names.
WHERE
Specifies the rows to be deleted. The clause can be omitted, or a search-condition or cursor-name can be specified. If the clause is omitted, all rows of the table or view are deleted.
search-condition
Is any search condition as described in Search conditions. Each column-name in the search-condition, other than in a subquery, must identify a column of the table or view.

The search-condition is applied to each row of the table or view and the deleted rows are those for which the result of the search-condition is true.

If the search-condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a row, and the results of the subquery used in applying the search condition. In actuality, a subquery with no correlated references may be executed only once, whereas a subquery with a correlated reference may have to be executed once for each row.

If a subquery refers to the object table of the DELETE statement or a dependent table with a delete rule of CASCADE, SET NULL, or SET DEFAULT, the subquery is completely evaluated before any rows are deleted.

CURRENT OF cursor-name
Identifies the cursor to be used in the delete operation. The cursor-name must identify a declared cursor as explained in the Notes for the DECLARE CURSOR statement.

The table or view identified must also be specified in the FROM clause of the select-statement of the cursor and the cursor must be deletable. For an explanation of deletable cursors, see DECLARE CURSOR.

When the DELETE statement is executed, the cursor must be positioned on a row; that row is the one deleted. After the deletion, the cursor is positioned before the next row of its result table. If there is no next row, the cursor is positioned after the last row.

isolation-clause
Specifies the isolation level to be used for this statement.
WITH

Introduces the isolation level, which may be one of:

  • RR Repeatable read
  • RS Read stability
  • CS Cursor stability
  • UR Uncommitted read
  • NC No commit
If isolation-clause is not specified the default isolation is used. See isolation-clause for a description of how the default is determined.