Deletes rows from the database.
DELETE [ row-limitation ] [ FROM ] [ owner.]table-or-view [ [ AS ] correlation-name ] [ WHERE search-condition ] [ ORDER BY { expression | integer } [ ASC | DESC ], ... ] [ OPTION( query-hint, ... ) ]
DELETE [ row-limitation ] [ FROM ] [ owner.]table-or-view [ [ AS ] correlation-name ] [ FROM table-expression ] [ WHERE search-condition ] [ ORDER BY { expression | integer } [ ASC | DESC ], ... ] [ OPTION( query-hint, ... ) ]
table-or-view : identifier
row-limitation : FIRST | TOP { ALL | limit-expression } [ START AT startat-expression ] limit-expression : simple-expression startat-expression : simple-expression simple-expression : integer | variable | ( simple-expression ) | ( simple-expression { + | - | * } simple-expression )
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 | number
row-limitation clause The row limitation clause allows you to restrict the rows being deleted to only a subset of the rows that satisfy the WHERE clause. The TOP and START AT arguments can be simple arithmetic expressions over host variables, integer constants, or integer variables. The TOP argument must evaluate to a value greater than or equal to 0. The START AT argument must evaluate to a value greater than 0. When specifying these clauses, an ORDER BY clause is required to order the rows in a meaningful manner.
FROM clause The FROM clause indicates the table from which rows will be deleted. In Syntax 2, the second FROM clause in the DELETE statement determines the rows to be deleted from the specified table based on joins with other tables. table-expression can contain arbitrarily complex table expressions, including derived tables and KEY and NATURAL joins.
The following examples illustrate how correlation names are matched when Syntax 2 is used. With this statement:
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's table-expression.
ORDER BY clause Specifies the sort order for the rows to be deleted. 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:
OPTION( isolation_level = ... )
specification in the query text overrides all other means of specifying isolation level for a query.
Deleting a significant amount of data using the DELETE statement causes an update to column statistics.
If you want to delete all of the rows of a table, consider using the more efficient TRUNCATE TABLE statement.
DELETE operations can be performed on views if the query specification defining the view is updatable. A view is updatable provided the SELECT statement defining the view has only one table in the FROM clause and does not contain a DISTINCT clause, a GROUP BY clause, a WINDOW clause, an aggregate function, or involve a set operator such as UNION or INTERSECT.
Must have DELETE permission on the table.
None.
SQL/2008 Syntax 1 is a core feature of the SQL/2008 standard, whereas Syntax 2 is a Transact-SQL vendor extension. The following features of Syntax 1 are vendor extensions:
The optional FROM keyword.
The row-limitation clause and the ORDER BY clause.
The OPTION clause.
Remove all data before 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.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |