|
Use this statement to delete rows from the database.
DELETE [ FIRST | TOP n ]
[ FROM ] [ owner.]table-name
[ FROM table-list ]
[ WHERE search-condition ]
[ ORDER BY { expression | integer } [ ASC | DESC ], ... ]
[ OPTION( query-hint, ... ) ]
query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value
option-name : identifier
option-value : hostvar (indicator allowed), string, identifier, or number
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 statement.
FIRST or TOP clause Primarily for use with the ORDER BY clause, this clause allows you to delete only a certain subset of the rows that satisfy the WHERE clause. The TOP value must be an integer constant or integer variable with value greater than or equal to 0. You cannot use a variable as input with TOP.
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 second FROM clause 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 ...
The table table_1 is identified without a correlation name in the first FROM clause, but with a correlation name 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 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.
Consider the following example:
DELETE FROM table_1 FROM table_1 AS alias_1, table_1 AS alias_2 WHERE ...
In this case, there are two instances of table_1 in the second FROM clause. The statement will fail with a syntax error as it is ambiguous which instance of the table_1 from the second FROM clause matches the first instance of table_1 in the first FROM clause.
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 This clause provides hints as to how to process the query. The following query hints are supported:
MATERIALIZED VIEW OPTIMIZATION 'option-value' Use the MATERIALIZED VIEW OPTIMIZATION clause to specify how the optimizer should make use of materialized views when processing the query. The specified option-value overrides the materialized_view_optimization database option for this query only. Possible values for option-value are the same values available for the materialized_view_optimization database option. See materialized_view_optimization option [database].
FORCE OPTIMIZATION When a query specification contains only simple queries (single-block, single-table queries that contain equality conditions in the WHERE clause that uniquely identify a specific row), it typically bypasses cost-based optimization during processing. In some cases you may want cost-based optimization to occur. For example, if you want materialized views to be considered during query processing, view matching must occur. However, view matching only occurs during cost-base optimization. If you want cost-based optimization to occur for a query, but your query specification contains only simple queries, specify the FORCE OPTIMIZATION option to ensure that the optimizer performs cost-based optimization on the query.
Similarly, specifying the FORCE OPTIMIZATION option in a SELECT statement inside of a procedure forces the use of the optimizer for any call to the procedure. In this case, plans for the statement are not cached. For more information on simple queries and view matching, see Phases of query processing, and Improving performance with materialized views.option-name = option-value Specify an option setting that takes precedence over any public or temporary option settings that are in effect, for this statement only. The supported options are:
Must have DELETE permission on the table.
None.
SQL/2003 Core feature. The use of more than one table in the FROM clause is a vendor extension.
Remove employee 105 from the database.
DELETE FROM Employees WHERE EmployeeID = 105;
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 );