Purpose

The Delete statement is used to delete rows from a table.

Syntax

DELETE FROM table_name [WHERE search_condition]

where:

table_name specifies the name of the table from which you want to delete rows.

search_condition is an expression that identifies which rows to delete from the table.

The Where clause determines which rows are to be deleted. Without a Where clause, all rows of the table are deleted, but the table is left intact. See Where clause for information about the syntax of Where clauses. Where clauses can contain subqueries.

Example A

This example shows a Delete statement on the emp table.

DELETE FROM emp WHERE emp_id = 'E10001'

Each Delete statement removes every record that meets the conditions in the Where clause. In this case, every record having the employee ID E10001 is deleted. Because employee IDs are unique in the employee table, at most, one record is deleted.

Example B

This example shows using a subquery in a Delete clause.

DELETE FROM emp WHERE dept_id = (SELECT dept_id FROM dept WHERE dept_name = 'Marketing')

The records of all employees who belong to the department named Marketing are deleted.