Deleting data, DELETE operator

When data is no longer needed, it gets deleted: a product is removed from the list, a cancelled reservation is erased. Rows are deleted with the DELETE operator, and a whole table can also be cleared with TRUNCATE. Note that emptying a table with TRUNCATE cannot always be undone.

General query structure with the DELETE operator

MySQL 8.4

DELETE FROM table_name
[WHERE condition];

[ ]optional part

table_name: The table from which records are deleted.
condition: Which rows to delete. Without WHERE, every row of the table is deleted.
Optional

For example, there is not a single payment for the vacuum cleaner in the Payments table, so it can safely be removed from the Goods list:

MySQL 8.4
DELETE FROM Goods
WHERE good_name = 'vacuum cleaner';

good_id	good_name	type
10 10 more rows	pineapples	3
11	television	8
12	~~vacuum cleaner~~	8
13	jacket	7
14 5 more rows	fur coat	7

The WHERE condition is written the same way as in a SELECT query: you can compare values, combine conditions with AND and OR, and use IN, LIKE, and subqueries.

Try it yourself: choose a condition, see which rows would be deleted, and then run the query.

WHERE

MySQL 8.4

DELETE FROM Goods
WHERE type = 2;

Rows matching the condition3

good_id	good_name	type
3	bread	2
4	milk	2
5	red caviar	3
6	cinema	4
9	potato	2
10	pineapples	3
13	jacket	7
14	fur coat	7

A query without a condition empties the whole table, while the table itself remains:

MySQL 8.4
DELETE FROM Payments;

You can also delete all records of a table using the TRUNCATE operator. It does not go through the rows one by one like DELETE, but empties the whole table at once, so it is much faster, especially on large tables.

General structure of a query with the TRUNCATE operator

MySQL 8.4

TRUNCATE TABLE table_name;

table_name: The table to clear completely.

At the same time, TRUNCATE behaves differently from DELETE:

You cannot specify a WHERE condition — the whole table is emptied
Delete triggers do not fire
The action cannot be undone: TRUNCATE immediately commits the current transaction, and ROLLBACK does not bring the rows back
Resets the AUTO_INCREMENT counter to the initial value
Does not work if the table is referenced by foreign keys of other tables
To use it, you need the DROP privilege on the table

You cannot specify a WHERE condition — the whole table is emptied
Row delete triggers are not processed — only TRUNCATE triggers fire, if any are defined
The action can be undone: inside a transaction, ROLLBACK brings the deleted rows back
Can reset the identifier counter when using the RESTART IDENTITY option (CONTINUE IDENTITY is used by default, so the counter is not reset)
If the table is referenced by foreign keys of other tables, you need to add the CASCADE option — then those tables are emptied as well
To use it, you need the TRUNCATE privilege on the table

To learn more about how DELETE differs from TRUNCATE and DROP, read the article TRUNCATE vs DELETE vs DROP in SQL: The Difference and What to Choose.

Deleting based on data from another table

Sometimes the rows to delete have to be selected using data from another table. Suppose the service no longer works with homes that have no kitchen, and the reservations for such homes have to be deleted. Information about the kitchen is stored in the Rooms table, and reservations are stored in the Reservations table.

The simplest way is a subquery in WHERE that returns the IDs of the matching homes:

MySQL 8.4
DELETE FROM Reservations
WHERE room_id IN (
    SELECT id
    FROM Rooms
    WHERE has_kitchen = false
);

You can do the same with JOIN. In this case, list the tables you delete records from right after DELETE.

MySQL 8.4

DELETE table_name_1 [, table_name_2] FROM
table_name_1 JOIN table_name_2
ON join_condition
[WHERE condition];

[ ]optional part

table_name_1: The table from which records are deleted. Its name goes both after DELETE and in the JOIN.
table_name_2: The joined table whose data helps select the records to delete. If you also list it after DELETE, records are deleted from both tables.
join_condition: How the rows are linked, for example table_name_1.field = table_name_2.field.
condition: Which records to delete. The condition can check columns of both tables.
Optional

You can do the same with USING. After it, specify the additional tables that help select the rows to delete.

MySQL 8.4

DELETE FROM table_name_1
USING table_name_2
WHERE join_condition
[AND condition];

[ ]optional part

table_name_1: The table from which records are deleted.
table_name_2: An additional table whose data helps select the records to delete. Nothing is deleted from it.
join_condition: How the rows are linked, for example table_name_1.field = table_name_2.field.
condition: An extra condition: which of the linked records to delete. It can check columns of both tables.
Optional

For our task, the query looks like this:

MySQL 8.4
DELETE Reservations FROM
Reservations JOIN Rooms ON
Reservations.room_id = Rooms.id
WHERE Rooms.has_kitchen = false;

MySQL 8.4
DELETE FROM Reservations
USING Rooms
WHERE Reservations.room_id = Rooms.id
AND Rooms.has_kitchen = false;

Now let's delete the homes themselves along with the reservations.

MySQL 8.4
DELETE Reservations, Rooms FROM
Reservations JOIN Rooms ON
Reservations.room_id = Rooms.id
WHERE Rooms.has_kitchen = false;

This query deletes only the homes that had reservations: Rooms rows without a match in Reservations do not get into the join.

The DELETE operator removes rows from only one table, so let's delete them one by one: first the bookings, then the housing itself. To make them run together or not at all, combine them into a transaction:

MySQL 8.4
BEGIN;
DELETE FROM Reservations
USING Rooms
WHERE Reservations.room_id = Rooms.id
AND Rooms.has_kitchen = false;

DELETE FROM Rooms
WHERE Rooms.has_kitchen = false;
COMMIT;

The second query deletes all housing without a kitchen, including housing that has never been booked.

A subquery cannot read the same table that DELETE removes rows from. For example, you cannot delete reservations cheaper than the average price this way:

MySQL 8.4
DELETE FROM Reservations
WHERE price < (SELECT AVG(price) FROM Reservations);

The query fails with an error:

MySQL 8.4
ERROR 1093 (HY000): You can't specify target table 'Reservations' for update in FROM clause

You can work around this by wrapping the subquery in one more SELECT. The average price is then first calculated and stored in a temporary table, and the deletion reads from it:

MySQL 8.4
DELETE FROM Reservations
WHERE price < (
    SELECT avg_price
    FROM (SELECT AVG(price) AS avg_price FROM Reservations) AS stats
);

Test yourself

You need to delete only the reservations cheaper than 100 from the Reservations table. Which query does this?