# Delete (SQL)

> Mediated Wiki article. Canonical URL: https://mediated.wiki/source/Delete_(SQL)
> Markdown URL: https://mediated.wiki/source/Delete_(SQL).md
> Source: https://en.wikipedia.org/wiki/Delete_(SQL)
> Source revision: 1351768735
> License: Creative Commons Attribution-ShareAlike 4.0 International (https://creativecommons.org/licenses/by-sa/4.0/)

SQL statement

"DELETE" redirects here. For other uses, see [Delete](/source/Delete_(disambiguation)).

In the database structured query language ([SQL](/source/SQL)), the **DELETE** statement is used to remove one or more rows from a [table](/source/Table_(database)).[1]: 428 A subset may be defined for deletion using a condition, otherwise all records are removed.[2] Some [database management systems](/source/Database_management_systems) (DBMSs), like [MySQL](/source/MySQL), allow deletion of rows from multiple tables with one DELETE statement (this is sometimes called multi-table DELETE).

It is not possible to delete individual values using DELETE, instead [UPDATE](/source/Update_(SQL)) should be used to set the value to NULL.[1]: 428 Backups should always be created before using DELETE.[1]: 429

## Examples

Delete [rows](/source/Row_(database)) from table *pies* where [column](/source/Column_(database)) *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](/source/Many-to-many_(data_model))). 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*. 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;

## Features

- It is a DML (Data Manipulation Language) command, therefore the following commands are used for this command: COMMIT and ROLLBACK

- Deletes all or some records from the table, you can limit the records to be deleted by using the WHERE clause

- Does not free the space occupied by the data in the table (in the TABLESPACE)

- Does not reset the SEQUENCE value assigned to the table

- DELETE works much slower than TRUNCATE

- You can undo the operation of removing records by using the ROLLBACK command

- DELETE requires a shared table lock

- Triggers fire

- DELETE can be used in the case of: database link

- DELETE returns the number of records deleted

- Transaction log - DELETE needs to read records, check constraints, update block, update indexes, and generate redo / undo. All of this takes time, hence it takes time much longer than with TRUNCATE

- reduces performance during execution - each record in the table is locked for deletion

- DELETE uses more transaction space than the TRUNCATE statement

- DELETE can be used with indexed views

- DELETE generates a small amount of redo and a large amount of undo

- DELETE operation does not make unusable indexes usable again

## Related commands

Deleting all rows from a table can be very time-consuming. Some [DBMS](/source/DBMS)[*[clarification needed](https://en.wikipedia.org/wiki/Wikipedia:Please_clarify)*] offer a [TRUNCATE TABLE](/source/Truncate_(SQL)) 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](/source/Drop_(SQL)) command can be used.

## References

1. ^ [***a***](#cite_ref-phpandmysqlbook_1-0) [***b***](#cite_ref-phpandmysqlbook_1-1) [***c***](#cite_ref-phpandmysqlbook_1-2) Jon Duckett; Emme Stone; Chris Ullman (2022). [*PHP & MySQL: server-side web development*](https://phpandmysql.com/buy/). [Hoboken, New Jersey](/source/Hoboken%2C_New_Jersey): John Wiley & Sons, Inc. [ISBN](/source/ISBN_(identifier)) [978-1-119-14921-7](https://en.wikipedia.org/wiki/Special:BookSources/978-1-119-14921-7). [LCCN](/source/LCCN_(identifier)) [2021951353](https://lccn.loc.gov/2021951353). [Archived](https://web.archive.org/web/20250430043459/https://phpandmysql.com/buy/) from the original on 30 April 2025. Retrieved 28 April 2026.

1. **[^](#cite_ref-2)** ["SQL Delete Statement"](https://www.w3schools.com/Sql/sql_delete.asp). W3Schools.

## External links

- [Truncate vs Delete SQL Databases](https://rozwoj-oprogramowania.pl/en/blog/databases/truncate-vs-delete.html) gives examples of DELETE statements features

v t e SQL Versions SEQUEL SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2006 SQL:2008 SQL:2011 SQL:2016 SQL:2023 Keywords As Case Create Delete From Group by Having Insert Join Merge Null Order by Over Prepare Select Truncate Union Update With Related Edgar Codd Relational database ISO/IEC SQL parts Framework Foundation Call-Level Interface Persistent Stored Modules Management of External Data Object Language Bindings Information and Definition Schemas SQL Routines and Types for the Java Programming Language XML-Related Specifications

---
Adapted from the Wikipedia article [Delete (SQL)](https://en.wikipedia.org/wiki/Delete_(SQL)) by Wikipedia contributors ([contributor history](https://en.wikipedia.org/wiki/Delete_(SQL)?action=history)). Available under [Creative Commons Attribution-ShareAlike 4.0 International](https://creativecommons.org/licenses/by-sa/4.0/). Changes may have been made.