DELETE statement

Use this statement to delete rows from the database.

Syntax

DELETE [ row-limitation ] 
[ FROM ] [ owner.]table-expression
[ FROM table-list [,...] ]
[ WHERE search-condition ]
[ ORDER BY { expression | integer } [ ASC | DESC ], ... ]
[ OPTION( query-hint, ... ) ]

table-list : 
table-name [,...]

table-name : 
[ owner.]base-table-name [ [ AS ] correlation-name ]
| [ owner.]view-name [ [ AS ] correlation-name ]
| derived-table

derived-table : 
( select-statement ) 
[ AS ] correlation-name [ ( column-name [,... ] ) ]

row-limitation :
  FIRST | TOP n [ START AT m ]

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| FORCE NO OPTIMIZATION
| option-name = option-value

table-expression : A full table expression that can include joins. See FROM clause.

option-name : identifier

option-value : hostvar (indicator allowed), string, identifier, or number

Parameters

row-limitation clause The row limiting clause allows you to return only a subset of the rows that satisfy the WHERE clause. The TOP and START AT values can be a host variable, integer constant, or integer variable. The TOP value must be greater than or equal to 0. The START AT value must be greater than 0. Normally, when specifying these clauses, an ORDER BY clause is specified as well to order the rows in a meaningful manner. See Explicitly limiting the number of rows returned by a query.

FROM clause The FROM clause indicates the table from which to delete rows. The second FROM clause in the DELETE statement qualifies the rows to be deleted from the specified table based on joins. If the second FROM clause is present, the WHERE clause qualifies the rows of this second FROM clause.

The FROM table-expression clause allows deletions based on joins. table-expression can contain arbitrary complex table expressions, such as KEY and NATURAL joins. For a full description of the FROM clause and joins, see FROM clause.

The following statement illustrates a potential ambiguity in table names in DELETE statements with two FROM clauses that use correlation names:

DELETE
FROM table_1
FROM table_1 AS alias_1, table_2 AS alias_2
WHERE ...

table table_1 doesn't have a correlation name in the first FROM clause but does in the second FROM clause. In this case, table_1 in the first clause is identified with alias_1 in the second clause—there is only one instance of table_1 in this statement. This is allowed as an exception to the general rule that where a table is identified with a correlation name and without a correlation name in the same statement, two instances of the table are considered.

However, in the following example, there are two instances of table_1 in the second FROM clause. The statement fails with a syntax error because it is not clear which instance of the table_1 from the second FROM clause matches the first instance of table_1 in the first FROM clause.

DELETE
FROM table_1
FROM table_1 AS alias_1, table_1 AS alias_2
WHERE ...

WHERE clause The DELETE statement deletes all the rows that satisfy the conditions in the WHERE clause. If no WHERE clause is specified, all rows from the named table are deleted. If a second FROM clause is present, the WHERE clause qualifies the rows of the second FROM clause.
ORDER BY clause Specifies the sort order for the rows. Normally, the order in which rows are updated does not matter. However, in conjunction with the FIRST or TOP clause the order can be significant.
You cannot use ordinal column numbers in the ORDER BY clause.

Each item in the ORDER BY list can be labeled as ASC for ascending order (the default) or DESC for descending order.
OPTION clause Use this clause to specify hints for executing the statement. The following hints are supported:
- MATERIALIZED VIEW OPTIMIZATION option-value
- FORCE OPTIMIZATION
- FORCE NO OPTIMIZATION
- option-name = option-value
For a description of these options, see the OPTIONS clause of the SELECT statement.

Remarks

Deleting a significant amount of data using the DELETE statement causes an update to column statistics.

The DELETE statement can be used on views, provided the SELECT statement defining the view has only one table in the FROM clause and does not contain a GROUP BY clause, an aggregate function, or involve a UNION clause.

DELETEs can be performed on views if the query specification defining the view is updatable. For more information about identifying views that are inherently non-updatable, see Working with regular views.

Permissions

Must have DELETE permission on the table.

Side effects

None.

Standards and compatibility

SQL/2003 Core feature. The use of more than one table in the FROM clause is a vendor extension.

Example

Remove all data prior to 2000 from the FinancialData table.

DELETE
FROM FinancialData
WHERE Year < 2000;

Remove the first 10 orders from SalesOrderItems table where ship date is older than 2001-01-01 and their region is Central.

DELETE TOP 10
FROM SalesOrderItems
FROM SalesOrders
WHERE SalesOrderItems.ID = SalesOrders.ID
  and ShipDate < '2001-01-01' and Region ='Central'
ORDER BY ShipDate ASC;

Remove department 600 from the database, executing the statement at isolation level 3.

DELETE FROM Departments
WHERE DepartmentID = 600
OPTION( isolation_level = 3 );

Discuss this page in DocCommentXchange.
Send feedback about this page using email.