O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required