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

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

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.