The DELETE statement removes any number of records from a table.
Required Privileges
To delete rows from a table, the user authorization must
own the table, own or possess OWNER privileges on the schema to which the
table belongs, possess DELETE privileges on the table, or possess DBA privileges.
Syntax
table name
The table name argument identifies an existing table. If the table
is not in the current schema, prefix the table name with the name of the appropriate
schema followed by a period ( . ), that is, schema-name.table-name.
Boolean value expression
In the WHERE clause, a Boolean value expression defines a deletion criterion
(or criteria) applied to each row of data values in the table specified by
the DELETE statement. If the data in a particular record matches the selection
criteria, then that record is removed from the table; otherwise the record
is passed over. See the section Selection
Criteria for WHERE and HAVING Clauses for more information about Boolean
value expressions.
Description
A DELETE statement deletes zero or more rows from a table; an optional WHERE clause can be included to indicate the records to be deleted. The WHERE condition is defined in the same way as it would be for a SELECT command. To determine which records a searched DELETE command will delete, first execute a SELECT statement on the table using the same WHERE condition.
WHERE clause
The WHERE clause identifies the rows to be deleted by the DELETE statement. The search condition following the WHERE keyword is applied to each row of the named table, deleting all rows for which the search condition is true.
CAUTION!
If no WHERE condition is specified, all records in the table are deleted. If a COMMIT statement is subsequently issued, these records cannot be recovered. After such an unqualified DELETE command is issued, the table will not contain any active records; however the table will still exist and will still appear in the system tables for the database. New data can be entered into it at any time using the INSERT command.
Examples
In the following DELETE statement, partsupp is the parts supplier table and sno is the supplier number:
DELETE FROM partsupp
WHERE sno = "S1";
This command deletes all the records in which the supplier is S1.
The more complex example shown below deletes all suppliers who supply part P4:
DELETE FROM supplier
WHERE sno IN (SELECT sno
FROM
partsupp
WHERE pno = "P4");
If the optional WHERE clause is not included, all records in the table are
deleted and are not recoverable after a COMMIT. For example, the following
command removes all of the records from the SUPPLIER table:
DELETE FROM supplier;