Delete (SQL)

In the database structured query language ( SQL), the 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. Some database management systems (DBMSs), like MySQL, allow deletion of rows from multiple tables with one DELETE statement (this is sometimes called multi-table DELETE).

Examples

Delete rows from table ''pies'' where column ''flavor'' equals ''Lemon Meringue'':

DELETE FROM pies
WHERE flavor='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
);

Example with related tables

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:

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. 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;

Related commands

Deleting all rows from a table can be very time-consuming. Some DBMS offer a TRUNCATE TABLE command that works a lot quicker, as it only alters metadata and typically does not spend time enforcing constraints or firing triggers.

DELETE only deletes the rows. For deleting a table entirely the DROP command can be used.

References

{{DEFAULTSORT:Delete (Sql)
SQL keywords
Articles with example SQL code