The MySQL/MariaDB DELETE Statement – How to Use It

Being able to delete data from a table in a database is a pretty important thing – This article will show you how it’s done in MySQL/MariaDB.

MySQL DELETE Statement Syntax

In its basic usage, the DELETE operator is used in conjunction with a WHERE query to delete records matching that query:

DELETE FROM table WHERE query;

Note that:

• table is the table you wish to delete records from
• query is the query that defines the conditions records should match to be deleted

Safety First!

Please make sure you check your DELETE query carefully before executing it! Unfortunately, once the records have been deleted, there’s no undo button to get them back.

If you’re working on anything important, it’s probably wise to take a backup before getting to work so that you can restore it if something goes wrong and something that wasn’t meant to be deleted was.

MySQL DELETE Example

The below example uses the following table, which contains some details about fruit:

fruit table:

Simple syntax means a simple example. Below, all records where the column color contains the value yellow will be deleted:

DELETE FROM fruit WHERE colour="yellow";

All matches to the query will be removed – in this case, the records for lemons and bananas match and will be deleted.

If no records match the query, no records will be deleted.

Single items can be removed by specifying a unique column in the query:

DELETE FROM fruit WHERE id=4;

For example, the above query will remove the entry for kiwi fruit as it is the only record with the matching unique id.

For more information on how the DELETE statement can be used with more complex queries, check out the official MySQL documentation.

If you need to empty a table of all data completely, check out the TRUNCATE MySQL statement.