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;
- table is the table you wish to delete records from
- query is the query that defines the conditions records should match to be deleted
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:
|3||banana||yellow||not that tasty|
|4||kiwi fruit||not tasty at all|
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.