One of the must-have SQL queries is DELETE. And with good reason: this command lets you delete rows of data. But how do you use SQL DELETE? Let's find out in this article.
What is SQL DELETE?
To guarantee the quality and reliability of the data in a table, it is often necessary to delete one or more items of data (either because they are false, obsolete, erroneous, inadequate, etc.). To this end, you can use the SQL DELETE query. This command allows you to delete all records you no longer need.
Good to know: before deleting anything from a database or table, we recommend that you perform a backup. Once a DELETE request has been launched, there’s no going back. So if you want to recover data that has been inadvertently deleted, it’s best to have a backup.
How do I use SQL DELETE to delete data?
The syntax for deleting data from a table is as follows:
DELETE FROM `table`
WHERE condition
And to help you better understand the use of SQL DELETE, we’ve put together a number of concrete application cases. To this end, we use the “Client” table below:
| ID | Vorname | Nachname | Stadt | Alter | |
|---|---|---|---|---|---|
| 1 | Justin | Martin | Paris | 29 | justinemartin@gmail.com |
| 2 | Thomas | Durant | Bordeaux | 35 | tomtom@sfr.fr |
| 3 | Marie | Leroy | Angers | 27 | marieleroy@laposte.net |
| 4 | Vanessa | Savary | Marseille | 32 | vanessa13@gmail.com |
Delete a single line
If you wish to delete a single line, simply register the following query:
DELETE FROM `Client`
WHERE `id` = 2
In this example, we’ve deleted the row in the Customer table corresponding to ID 2. Here’s the result:
| ID | Vorname | Nachname | Stadt | Alter | |
|---|---|---|---|---|---|
| 1 | Justin | Martin | Paris | 29 | justinemartin@gmail.com |
| 2 | Thomas | Durant | Bordeaux | 35 | tomtom@sfr.fr |
| 3 | Marie | Leroy | Angers | 27 | marieleroy@laposte.net |
| 4 | Vanessa | Savary | Marseille | 32 | vanessa13@gmail.com |
Delete several lines
You can easily delete several lines if they have common characteristics.
For example, delete all customers over 30 years old.
Here’s the query:
DELETE FROM `Customer`
WHERE ` ge` > ’30’
And the result:
| ID | Vorname | Nachname | Stadt | Alter | |
|---|---|---|---|---|---|
| 1 | Justin | Martin | Paris | 29 | justinemartin@gmail.com |
| 3 | Marie | Leroy | Angers | 27 | marieleroy@laposte.net |
As a reminder, the WHERE clause allows you to specify the table rows you wish to delete.
Delete all data
The syntax is as follows:
DELETE FROM `tableau`
Simply remove the WHERE conditional clause to remove all data from your table.
Good to know: instead of the DELETE request, you can also use the TRUNCATE command. The operation is identical, with one difference: if there is an auto-increment, the TRUNCATE query resets it. This is not the case with the DELETE command.
Delete rows between two datasets
The DELETE query can be used in conjunction with other SQL commands. In this example, we also show you the BETWEEN and AND queries. The idea is to delete the lines between two other lines. For example, between lines 1 and 4.
The syntax is as follows
DELETE FROM Client WHERE id BETWEEN 1 AND 4.
Here’s the result:
| ID | Prénom | Nom | Ville | Âge | |
|---|---|---|---|---|---|
| 1 | Justin | Martin | Paris | 29 | justinemartin@gmail.com |
| 4 | Vanessa | Savary | Marseille | 32 | vanessa13@gmail.com |
These are just a few simplified examples. In reality, the SQL DELETE command has many applications. If you’d like to learn more, join our training program.
Join the DataScientest training course to learn more about SQL language
Mastering SQL is one of the fundamentals for any data expert or developer. But between updates, deletions, additions… you need to know a multitude of SQL queries.
That’s why we offer training courses at DataScientest. In addition to the SQL DELETE command, you’ll learn all the queries that are essential for handling a relational database.
