SQL DELETE Syntax – Listed by DBMS

by

This article lists the SQL DELETE syntax, as implemented by various database management systems (DBMSs). The syntax is listed exactly as each vendor has listed it on their website. Click on the applicable link to view more detail about the syntax for a particular vendor.

The DBMSs covered are MySQL, SQL Server, PostgreSQL, and Oracle Database.

MySQL

From the MySQL 5.7 Reference Manual.

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

SQL Server

From the Transact-SQL Reference:

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
    { { table_alias
      | <object> 
      | rowset_function_limited 
      [ WITH ( table_hint_limited [ ...n ] ) ] } 
      | @table_variable
    }
    [ <OUTPUT Clause> ]
    [ FROM table_source [ ,...n ] ] 
    [ WHERE { <search_condition> 
            | { [ CURRENT OF 
                   { { [ GLOBAL ] cursor_name } 
                       | cursor_variable_name 
                   } 
                ]
              }
            } 
    ] 
    [ OPTION ( <Query Hint> [ ,...n ] ) ] 
[; ]

<object> ::=
{ 
    [ server_name.database_name.schema_name. 
      | database_name. [ schema_name ] . 
      | schema_name.
    ]
    table_or_view_name 
}

PostgreSQL

From the PostgreSQL 9.5 Manual:

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Oracle Database

From the Oracle Database Online Documentation 12c Release 1 (12.1):

DELETE [ hint ]
   [ FROM ]
   { dml_table_expression_clause
   | ONLY (dml_table_expression_clause)
   } [ t_alias ]
     [ where_clause ]
     [ returning_clause ]
     [error_logging_clause];

 DML_table_expression_clause::=

{ [ schema. ]
  { table
    [ partition_extension_clause
    | @ dblink
    ]
  | { view | materialized view } [ @ dblink ]
  }
| ( subquery [ subquery_restriction_clause ] )
| table_collection_expression
}

partition_extension_clause::=

{ PARTITION (partition)
| PARTITION FOR (partition_key_value [, partition_key_value]...)
| SUBPARTITION (subpartition)
| SUBPARTITION FOR (subpartition_key_value [, subpartition_key_value]...)
}

subquery_restriction_clause::=

WITH { READ ONLY
     | CHECK OPTION 
     } [ CONSTRAINT constraint ]

table_collection_expression::=

TABLE (collection_expression) [ (+) ]

where_clause::=

WHERE condition

returning_clause ::=

{ RETURN | RETURNING } expr [, expr ]...
INTO data_item [, data_item ]...

error_logging_clause ::=

LOG ERRORS 
  [ INTO [schema.] table ]
  [ (simple_expression) ]
  [ REJECT LIMIT { integer | UNLIMITED } ]

About the DELETE Statement

The DELETE statement is a data manipulation language (DML) statement that removes the specified rows from a table.

The DELETE statement deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, all rows in the table will be deleted, leaving the table empty.

Tip

The TRUNCATE statement (or in the case of SQL Server, the TRUNCATE TABLE statement ) is similar to the DELETE  statement with no WHERE  clause; however, TRUNCATE is faster and uses fewer system and transaction log resources.