The MySQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax


DELETE FROM table_name WHERE condition; 

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!


Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1
Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden


SQL DELETE Example

The following SQL statement deletes the customer “Alfreds Futterkiste” from the “Customers” table:

Example

 DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste'; 

The “Customers” table will now look like this:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

Delete All Records

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will 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;