The SQL DELETE Statement

A SQL DELETE statement removes rows of data from a table. If the DELETE activity is successful, it returns the number of rows removed from the table.

If the DELETE activity is not successful, 0 is returned as the number of rows removed from the table.

Transactions are not supported for DELETE statements and you cannot create a DELETE statement from a transacted session.

Since transactions are not supported with DELETE, if an error occurs while performing row deletion, any rows already deleted remain deleted and the delete is not rolled back.

Syntax

DELETE FROM <table_name> [ AS <alias> ] [ <WHERE clause> ]

Where

  • table_name: Name of the table in which the rows are removed.
  • alias: A temporary name for the table that is used to make the table name more readable.
  • WHERE clause: An expression that starts with the keyword 'WHERE' and results in a boolean value that indicates whether a row should be removed. If omitted, all rows of the table are removed. For more information, see The WHERE Clause.
Warning: If a WHERE clause is omitted from a DELETE statement, all rows in the table are removed. For information on how to prevent the inadvertent removal of rows by DELETE statements without a WHERE clause, see the full_table_delete table and grid properties.