SQL in a Nutshell, 2nd Edition

Name

DELETE Statement

Synopsis

The DELETE statement erases records from a specified table or tables. DELETE statements acting against tables are sometimes called search deletes. The DELETE statement may also be used in conjunction with a cursor. DELETE statements acting upon the rows of a cursor are sometimes called positional deletes.

Platform	Command
DB2	Supported, with variations
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported
SQL Server	Supported, with limitations

SQL2003 Syntax

DELETE FROM { table_name | ONLY (table_name) }
[{ WHERE search_condition | WHERE CURRENT OF cursor_name }]

Keywords

FROM table_name

Identifies the table, called table_name, from which rows will be deleted. The table_name assumes the current schema if one is not specified. You may alternately specify a single table view name. FROM is mandatory, except in the DELETE...WHERE CURRENT OF statement. When not using the ONLY clause, do not enclose the table_name in parentheses.

ONLY (table_name)

Restricts cascading of the deleted records to any subtables of the target table or view. ONLY effects only typed (object-oriented) tables and views. If used with a non-typed table or view, it is ignored and does not cause an error. If ONLY is used, you must enclose the table_name in parentheses.

WHERE search_condition

Defines search criteria for the DELETE statement using one or more search_condition clauses to ensure that only the target rows are deleted. Any legal WHERE