MySQL DELETE
The MySQL DELETE Statement
To remove already-existing entries from a table, use the DELETE statement.
DELETE Syntax
DELETE FROM table_name WHERE condition;
Note: Exercise caution while removing entries from a table! Take note of the DELETE statement’s WHERE clause. Which record or records should be deleted is specified in the WHERE clause. Every record in the table will be removed if the WHERE clause is left out!
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
SQL DELETE Example
The customer “Alfreds Futterkiste” is removed from the “Customers” table using the SQL statement that follows:
Example
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
The “Customers” table will now look like this:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Delete All Records
A table can have all of its rows removed without the table itself being removed. This implies that the characteristics, indexes, and table structure will all be intact:
DELETE FROM table_name;
The following SQL statement deletes all rows in the “Customers” table, without deleting the table:
Example
DELETE FROM Customers;
