DELETE Statement


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.




Supported, with variations


Supported, with variations


Supported, with variations



SQL Server

Supported, with limitations

SQL2003 Syntax

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


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

Get SQL in a Nutshell, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.