![DELETE (positioned) statement [ESQL] [SP]](arrow-right.gif){kind=icon}
Deletes rows from the database.
DELETE [ FROM ] [ owner.]table-name …[ FROM table-list ] …[ WHERE search-condition ]
Removes employee 105 from the database:
DELETE FROM Employees WHERE EmployeeID = 105
Removes all data prior to 1993 from the FinancialData table:
DELETE FROM FinancialData WHERE Year < 1993
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
DELETE deletes all the rows from the named table that satisfy the search condition. If no WHERE clause is specified, all rows from the named table are deleted.
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.
The optional second FROM clause in the DELETE statement allows rows to be deleted based on joins. 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.
The effects of a DELETE on a table can be passed on to any of the join indexes that reference that table through the SYNCHRONIZE JOIN INDEX command. For performance reasons, you should do as many deletes as possible before synchronizing the join indexes.
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.
This 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 this 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. There is no way of identifying which instance the first FROM clause should be identified with. The usual rules of correlation names apply, and table_1 in the first FROM clause is identified with neither instance in the second clause: there are three instances of table_1 in the statement.
None
Must have DELETE permission on the table.
