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.
Notes
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.
Notes
Delete is supported for primitive types and non-nested complex types. See "Complex Type Normalization" for details.
To enable Insert, Update, and Delete, set the ReadOnly connection property to false.
A Where clause can be used to restrict which rows are deleted.
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.