DELETE

Deletes a set of rows from a table or view.

 

Usage

Embedded, Interactive, Module, ODBC, Procedural, JDBC.

Description

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.

Language Elements

search-condition, see Search Conditions.

with-clause, see The WITH Clause.

Restrictions

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.

Notes

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 WORK option it is possible that some rows will be deleted).

Example

DELETE FROM countries

    WHERE CITY = 'Dublin';

Standard Compliance