DELETE Statement

Quick Links:

Syntax

DELETE 
   [ FROM ] [ owner.]table-name [[AS correlation-name]
   ...[ FROM table-expression ] 
   [ WHERE search-condition ]]

table-expression
   table-spec 
   | table-expression join-type table-spec [ ON condition ] 
   | table-expression, ...

Parameters

(back to top)

FROM clause – indicates the table from which rows will be deleted. The optional second FROM clause in the DELETE statement determines the rows to be deleted from the specified table based on joins with other tables. If the second FROM clause is present, the WHERE clause qualifies the rows of this second FROM clause. Rows are deleted from the table name given in the first FROM clause.
Note: You cannot use the DELETE statement on a join virtual table. If you attempt to delete from a join virtual table, an error is reported.

There is a potential ambiguity in table names in DELETE statements when the FROM clauses do not both use correlation names. Consider this example:
```
DELETE
FROM table_1
FROM table_1 AS alias_1, table_2 AS alias_2
WHERE ...
```
table_1 is identified without a correlation name in the first FROM clause, but with a correlation name in the second FROM clause. The use of a correlation name for table_1 in the second FROM clause ensures that only one instance of table_1 exists in the statement. This is an exception to the general rule that where the same table is identified with and without a correlation name in the same statement, two instances of the table are considered.

Now consider this example:
```
DELETE
FROM table_1
FROM table_1 AS alias_1, table_1 AS alias_2
WHERE ...
```
There are two instances of table_1 in the second FROM clause. Since there is no way to identify which instance the first FROM clause should be identified with, the general rule of correlation names mean that table_1 in the first FROM clause is identified with neither instance of table_1 in the second clause: there are three instances of table_1 in the statement.

WHERE clause – if specified, only rows satisfying the search condition are deleted. If no WHERE clause is specified, every row is deleted.

Examples

(back to top)

Example 1 – removes employee 105 from the database:
```
DELETE
FROM Employees
WHERE EmployeeID = 105
```
Example 2 – removes all data prior to 1993 from the FinancialData table:
```
DELETE
FROM FinancialData
WHERE Year < 1993
```

Example 3 – removes all names from the Contacts table if they are already present in the Customers table:

DELETE
FROM Contacts
FROM Contacts, Customers
WHERE Contacts.Surname = Customers.Surname
AND Contacts.GivenName = Customers.GivenName

Usage

(back to top)

DELETE 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 operation.

If the same table name from which you are deleting rows is used in both FROM clauses, they are considered to reference the same table if one of the following is true:

Both table references are not qualified by specifying a user ID
Both table references are qualified by specifying a user ID
Both table references are specified with a correlation name

In cases where the server cannot determine if the table references are identical, an error appears. This prevents the user from unintended semantics by deleting unintended rows.

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Sybase Database product—Supported by Adaptive Server, including the vendor extension.

Permissions

(back to top)

Requires DELETE privilege on the table.