Deletes a set of rows from a table or view.
All rows in the set defined by the WHERE clause are deleted from the base table or view identified by table-name. If no WHERE clause is specified, all rows are deleted.
If table-name identifies a view rather than a base table, entire rows, including columns invisible from the view, are deleted from the base table on which the view is defined. For a delete to be performed on a view, the view must be updatable, see CREATE VIEW.
A NOT FOUND condition code is returned if no row is deleted, see Return Status and Conditions.
search-condition, see Search Conditions.
DELETE privilege must be held on the table or view identified by table-name.
In a procedural usage context, the DELETE statement is only permitted if the procedure access-clause is MODIFIES SQL DATA, see CREATE PROCEDURE.
If a correlation-name is specified after the table name in the DELETE FROM clause, the correlation name must be used to refer to the table in the WHERE clause of the DELETE statement.
If the table name addressed by the DELETE statement is subject to any referential constraints, the delete operation must not create a situation where these constraints are violated. The effect of the delete operation on any referential constraints depends on the delete-rule in effect for each constraint, see CREATE TABLE for a description of the delete-rule options.
A DELETE statement is executed as a single statement. If an error occurs at any point during the execution, none of the rows in the set defined by the WHERE clause will be deleted (however, if the table is stored in a databank with the NULL option it is possible that some rows will be deleted).
ExampleDELETE FROM countries WHERE CITY = 'dublin';
EXTENDED Support for the AS correlation-name clause is a Mimer SQL extension.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40