SQL in a Nutshell

Name

DELETE

Synopsis

The DELETE statement erases records from a specified table or tables. It is a logged operation, meaning that it can be undone with a ROLLBACK command.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Supported, with variations
Oracle	Supported
PostgreSQL	Supported

Warning

It is rare to issue a DELETE statement without a WHERE clause, because this results in deleting all rows from the affected table.

SQL99 Syntax and Description

DELETE [FROM] [owner.]table_name [WHERE clause]

If it becomes necessary to remove all the rows in a table, it is preferable to use the TRUNCATE TABLE statement. In those databases that support the command, this is usually a faster method to physically remove all rows. TRUNCATE TABLE is faster than DELETE because TRUNCATE is not logged, making rollback impossible. The reduction of logging overhead saves considerable time when erasing a large number of records.

Microsoft SQL Server Syntax and Variations

DELETE [FROM] [owner.] {table_name | view_name} 
[WITH (query_hint[,...n]]
[FROM table_source[,...n]]
[WHERE clause | [CURRENT OF [GLOBAL] cursor_name]]
[OPTION (query_hint[,...n])]

Microsoft SQL Server allows records to be deleted both from tables and from views that describe a single table. (There are some other special rules that allow deletion from a multitable view, but they are quite complex and beyond the scope of this book.) At two points in the command, after the first FROM and at the end of the statement, SQL Server’s ...