SQL Reference Guide

DELETE

Description

Deletes zero, one or more rows from the specified table that satisfy the search condition specified in the WHERE clause. If the optional WHERE clause is not specified, then the DELETE statement deletes all rows of the specified table.

Syntax

DELETE FROM [owner_name.] { table_name | view_name }

[ WHERE search_condition ];

Notes

• If the table has primary/candidate keys, and if there exists references from other tables to the rows to be deleted, the statement is rejected.
• While our DELETE doesn't directly support a TOP clause, you can do this with any indexed field with a subquery as such:

DELETE FROM <table> WHERE <idxfield> <= (SELECT MAX(<idxfield>) FROM (SELECT TOP 1000 <idxfield> FROM <table>) x )

Example

DELETE FROM customer

WHERE customer_name = 'RALPH' ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege.
• Ownership of the table.
• DELETE permission on the table.

If the target is a view, then the DELETE privilege is required on the target base table referred to in the view definition.

SQL Compliance	SQL-92, ODBC Extended SQL grammar
Environment	Embedded SQL, interactive SQL, ODBC applications
Related Statements	Search Conditions