The DELETE statement removes any number of records from a table.
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.
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.
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.
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.
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.
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
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;