Once you understand and know the SQL queries to insert data into tables and update them with UPDATE, you need to know how to delete stored records.
Clear data with SQL It is quite simple, so much so that I can assure you that it is even dangerous. As it happened with the queries to update, if we don’t go with caution when deletingwe may end up with completely empty tables.
So that you can correctly learn all the facets, uses and keys to delete rows in tables, Next, I will go through, point by point, the following sections:
- What is the SQL query to delete data.
- Parts of a DELETE query.
- Simple examples of how to delete records with SQL.
- Advanced concepts about SQL DELETE queries.
- Examples of erasure with subqueries and EXIST or IN clauses.
- SQL query to delete records
- Parts of a DELETE query
- Examples of use to delete table rows with DELETE SQL
- Example 1 for beginners
- Example 2 DELETE with WHERE
- easy example 3
- Simple example 4 of deleting with DELETE
- Advanced uses of the DELETE statement
- Example 1 with DELETE and EXISTS
- Example 2 of DELETE with IN clause
SQL query to delete records
In SQL there is a statement for each task, and deleting data is no exception.
The DELETE statement will be the one we will use when we want to delete certain records or completely empty a table.
This delete statement completes a perfect trio with INSERT and UPDATE for full control over the information stored in our databases.
The most basic scheme of a DELETE query is the following:
DELETE FROM tabla;
Of course, if we use this version without further ado, we will remove all records from a table.
Parts of a DELETE query
The structure of any DELETE query is simple, you just have to be clear that normally we will always accompany a DELETE in SQL from a WHERE clause to help us delete only the desired records.
A complete DELETE query would have the following form:
DELETE FROM tabla WHERE condicion1 [condicion2] [condicionN];
As you can see, you can indicate as many conditions as you need to make a selection as complex as you need.
Besides, remember that the WHERE statement As in any other query, it must have the different conditions joined by AND or OR.
Examples of use to delete table rows with DELETE SQL
We have already seen the structure of a DELETE statement but how would they be DELETE queries for real? Let’s see it in the best way: with SQL examples and different variants of WHERE and operators.
I will do the following SQL examples on a table created with the following CREATE statement:
CREATE pieza_ordenador ( id INT, nombre VARCHAR(100) NOT NULL, tipo VARCHAR(50) NOT NULL, fabricante VARCHAR(50) NOT NULL, fecha_fabricacion DATE, fecha_garantia DATE, num_reparaciones INT DEFAULT 0 );
Do you already have the structure of the table clear? If so, let’s continue with the examples, otherwise I recommend that you read our tutorial on creating tables or the one on creating tables in an advanced way.
Example 1 for beginners
Delete all parts whose warranty date has already passed.
DELETE FROM pieza_ordenador WHERE fecha_garantia < CURDATE();
This deletion is executed on all parts with a warranty date less than the current date, this date is returned by the CURDATE() database function that returns the current date in the format YYYY-MM-DD.
Example 2 DELETE with WHERE
Delete all rows for manufacturer ‘intel’
DELETE FROM pieza_ordenador WHERE fabricante = ‘intel’;
easy example 3
Delete all rows that are from manufacturer ‘amd’ and with a manufacturing date before 2016.
DELETE FROM pieza_ordenador WHERE fabricante = ‘amd’ AND fecha_fabricacion < 2016-01-01;
Simple example 4 of deleting with DELETE
Delete all rows that have been repaired more than 2 times, that are of type ‘graphics card’ and that are not from manufacturer ‘amd’, or those that have been repaired more than 4 times.
DELETE FROM pieza_ordenador WHERE ( num_reparaciones > 2 AND tipo = ‘tarjeta gráfica’ AND fabricante <> ‘amd’ ) OR num_reparaciones > 4;
Advanced uses of the DELETE statement
as the SQL DELETE queries allow accompanying them with the WHERE clause it is also possible to perform all the types of WHERE that you know.
In the WHERE clause of a DELETE we can use subqueries and clauses such as EXISTS or IN to select records in a much more complex and configurable way.
Introducing this greater complexity in the queries becomes vital when our tables contain relationships between them and we want to perform conditions relating them.
To carry out the following examples I will now create a table buys related to the above table with the following CREATE:
CREATE TABLE compra ( id INT PRIMARY KEY AUTO_INCREMENT, id_pieza_ordenador INT REFERENCES pieza_ordenador (id), fecha_compra DATE, precio FLOAT );
Now I am going to show you several examples of DELETE SQL with subqueries and EXISTS and IN clauses.
Example 1 with DELETE and EXISTS
Deletion of the purchases of which the part is ‘amd’.
DELETE FROM compra WHERE EXISTS ( SELECT * FROM pieza_ordenador WHERE compra.id_pieza_ordenador = pieza_ordeandor.id AND fabricante = ‘amd’);
Example 2 of DELETE with IN clause
SQL Delete of all the pieces that were bought for a price between 20 and 50 euros:
DELETE FROM pieza_ordenador WHERE id IN ( SELECT id_pieza_ordenador FROM compra WHERE precio > 20 AND precio < 50 );