In the database structured query language (SQL

SQL

SQL is a programming language designed for managing data in relational database management systems ....

), the DELETE statement removes one or more records from a table

Table (database)

In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...

. A subset may be defined for deletion using a condition, otherwise all records are removed.

Usage

The DELETE statement follows the syntax:

DELETE FROM table_name [WHERE condition];

Any rows that match the WHERE condition

Where (SQL)

A WHERE clause in SQL specifies that a SQL Data Manipulation Language statement should only affect rows that meet specified criteria. The criteria are expressed in the form of predicates...

 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

Result set

An SQL result set is a set of rows from a database, as well as meta-information about the query such as the column names, and the types and sizes of each column. Depending on the database system, the number of rows in the result set may or may not be known. Usually, this number is not known up...

.

Executing a DELETE statement can cause triggers

Database trigger

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database...

 to run that can cause deletes in other tables. For example, if two tables are linked by a foreign key

Foreign key

In the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...

 and rows in the referenced table are deleted, then it is common that rows in the referencing table would also have to be deleted to maintain referential integrity

Referential integrity

Referential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....

.

Examples

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)

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:

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

Truncate (SQL)

In SQL, the TRUNCATE TABLE statement is a Data Definition Language operation that marks the extents of a table for deallocation . The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms...

 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.