From Wikipedia, the free encyclopedia

An SQL DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.

The DELETE statement has this syntax:

DELETE FROM table_name [WHERE condition]

Any rows that match the WHERE condition will be removed from the table. If the WHERE clause is omitted, all rows in the table are removed. The DELETE statement should thus be used with caution!

The DELETE statement does not return any rows; that is, it will not generate a result set.

Executing a DELETE statement may cause triggers to run that may cause deletes in other tables. For example, if two tables are linked by a foreign key and rows in one table were deleted, then it is common that rows in the second table would also have to be deleted to maintain referential integrity. For a more general discussion of how cascading deletes are handled, see propagation constraint.

Delete rows from table pies where the column flavour equals Lemon Meringue:

DELETE FROM pies WHERE flavour='Lemon Meringue';

Delete rows in trees, if the value of height is smaller than 80.

DELETE FROM trees WHERE height < 80;

Delete all rows from mytable:

DELETE FROM mytable;

Delete rows from mytable using a subquery in the where condition:

DELETE FROM mytable WHERE id IN (SELECT id FROM mytable2)

Delete rows from mytable using a list of values:

DELETE FROM mytable WHERE id IN (value1, value2, value3, value4, value5)

Suppose there is a simple database that lists people and addresses. More than one person can live at a particular address and a person can live at more than one address (this is an example of a many-to-many relationship). The database only has three tables, person, address, and pa, with the following data:

person

pid	name
1	Joe
2	Bob
3	Ann

address

aid	description
100	2001 Main St.
200	35 Pico Blvd.

pa

pid	aid
1	100
2	100
3	100
1	200

The pa table relates the person and address tables, showing that Joe, Bob and Ann all live at 2001 Main Street, but Joe also takes up residence on Pico Boulevard.

In order to remove joe from the database, two deletes must be executed:

DELETE FROM person WHERE pid=1
DELETE FROM pa WHERE pid=1

To maintain referential integrity, Joe's records must be removed from both person and pa. The means by which integrity is sustained can happen differently in varying relational database management systems^{[citation needed]}. It could be that beyond just having three tables, the database also has been set up with a trigger so that whenever a row is deleted from person any linked rows would be deleted from pa. Then the first statement:

DELETE FROM person WHERE pid=1

would automatically trigger the second:

DELETE FROM pa WHERE pid=1

v • d • e Database management systems
Database models · Database normalization · Database storage · Distributed DBMS · Referential integrity · Relational algebra · Relational calculus · Relational database · Relational DBMS · Relational model · Transaction processing
Concepts	Database · ACID · Null · Candidate key · Foreign key · Primary key · Superkey · Surrogate key
Objects	Trigger · View · Table · Cursor · Log · Transaction · Index · Stored procedure · Partition
SQL	Select · Insert · Update · Merge · Delete · Join · Union · Create · Drop · Begin work · Commit · Rollback · Truncate · Alter
Implementations	Relational · Flat file · Deductive · Dimensional · Hierarchical · Network  · Object-oriented · Object-relational · Temporal · XML data stores
Components	Concurrency control · Query language · Query optimizer · Query plan · ODBC · JDBC
Database products: Object-oriented (comparison) · Relational (comparison)