Eliminar datos: la sentencia DELETE

Cuando los datos dejan de ser necesarios, se eliminan: un producto se quita del catálogo, una reserva cancelada se borra. Las filas se eliminan con la sentencia DELETE, y para vaciar una tabla entera también existe la sentencia TRUNCATE. Eso sí, ese vaciado no siempre se puede deshacer.

Estructura general de una consulta con DELETE

MySQL 8.4

DELETE FROM nombre_tabla
[WHERE condicion_seleccion];

[ ]parte opcional

nombre_tabla: La tabla de la que se eliminan los registros.
condicion_seleccion: Qué filas se eliminan. Sin WHERE se eliminan todas las filas de la tabla.
Opcional

Por ejemplo, la aspiradora no tiene ni un solo pago en la tabla Payments, así que puedes quitarla sin problema del catálogo de productos Goods:

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

good_id	good_name	type
10 10 filas más	pineapples	3
11	television	8
12	~~vacuum cleaner~~	8
13	jacket	7
14 5 filas más	fur coat	7

La condición de WHERE se escribe igual que en una consulta SELECT: puedes comparar valores, combinar condiciones con AND y OR, y usar IN, LIKE y subconsultas.

Pruébalo tú mismo: elige una condición, mira qué filas se eliminarían y después ejecuta la consulta.

WHERE

MySQL 8.4

DELETE FROM Goods
WHERE type = 2;

Filas que cumplen la condición3

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

Una consulta sin condición vacía la tabla entera, pero la tabla en sí se mantiene:

MySQL 8.4
DELETE FROM Payments;

También puedes eliminar todos los registros de una tabla con la sentencia TRUNCATE. No recorre las filas una por una como DELETE, sino que vacía toda la tabla de golpe, por lo que es mucho más rápido, sobre todo en tablas grandes.

Estructura general de una consulta con TRUNCATE

MySQL 8.4

TRUNCATE TABLE nombre_tabla;

nombre_tabla: La tabla que se vacía por completo.

Eso sí, TRUNCATE no se comporta igual que DELETE:

No se puede indicar una condición WHERE: se vacía toda la tabla
No se activan los triggers de eliminación
La acción no se puede deshacer: TRUNCATE confirma de inmediato la transacción actual, y ROLLBACK no recupera las filas
Reinicia el contador AUTO_INCREMENT a su valor inicial
No funciona si las claves foráneas de otras tablas hacen referencia a la tabla
Para usar TRUNCATE necesitas el permiso DROP sobre la tabla

No se puede indicar una condición WHERE: se vacía toda la tabla
No se activan los triggers de eliminación de filas; solo se activan los triggers de TRUNCATE, si los hay
La acción se puede deshacer: dentro de una transacción, ROLLBACK recupera las filas eliminadas
Puede reiniciar el contador de identificadores si usas la opción RESTART IDENTITY (por defecto se usa CONTINUE IDENTITY y el contador no se reinicia)
Si las claves foráneas de otras tablas hacen referencia a la tabla, hay que añadir la opción CASCADE, y entonces también se vacían esas tablas
Para usar TRUNCATE necesitas el permiso TRUNCATE sobre la tabla

Para saber en detalle en qué se diferencia DELETE de TRUNCATE y DROP, lee el artículo TRUNCATE, DELETE y DROP en SQL: en qué se diferencian y cuál elegir.

Eliminar según los datos de otra tabla

A veces las filas que hay que eliminar se tienen que elegir según los datos de otra tabla. Supongamos que el servicio ya no trabaja con alojamientos sin cocina y hay que eliminar sus reservas. La información sobre la cocina está en la tabla Rooms, y las reservas, en la tabla Reservations.

La forma más sencilla es una subconsulta en WHERE que devuelva los identificadores de los alojamientos que cumplen la condición:

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

Lo mismo se puede hacer con JOIN. En ese caso, justo después de DELETE se indican las tablas de las que se eliminan los registros.

MySQL 8.4

DELETE nombre_tabla_1 [, nombre_tabla_2] FROM
nombre_tabla_1 JOIN nombre_tabla_2
ON condicion_combinacion
[WHERE condicion_seleccion];

[ ]parte opcional

nombre_tabla_1: La tabla de la que se eliminan los registros. Su nombre aparece tanto después de DELETE como en el JOIN.
nombre_tabla_2: La tabla unida según cuyos datos se eligen los registros que se eliminan. Si también la indicas después de DELETE, se eliminarán registros de ambas tablas.
condicion_combinacion: Cómo se relacionan las filas de las dos tablas, por ejemplo nombre_tabla_1.campo = nombre_tabla_2.campo.
condicion_seleccion: Qué registros se eliminan. La condición puede comprobar columnas de ambas tablas.
Opcional

Lo mismo se puede hacer con USING. Después de él se indican las tablas adicionales según las cuales se eligen los registros que se eliminan.

MySQL 8.4

DELETE FROM nombre_tabla_1
USING nombre_tabla_2
WHERE condicion_combinacion
[AND condicion_seleccion];

[ ]parte opcional

nombre_tabla_1: La tabla de la que se eliminan los registros.
nombre_tabla_2: Una tabla adicional según cuyos datos se eligen los registros que se eliminan. De ella no se elimina nada.
condicion_combinacion: Cómo se relacionan las filas de las dos tablas, por ejemplo nombre_tabla_1.campo = nombre_tabla_2.campo.
condicion_seleccion: Una condición adicional: cuáles de los registros relacionados se eliminan. Puede comprobar columnas de ambas tablas.
Opcional

Para nuestra tarea, la consulta queda así:

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;

Ahora eliminemos, junto con las reservas, también los propios alojamientos.

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

Esta consulta solo eliminará los alojamientos que tenían reservas: las filas de Rooms sin pareja en Reservations no entran en la combinación.

La sentencia DELETE solo elimina filas de una tabla, así que las eliminaremos por turnos: primero las reservas y después los propios alojamientos. Para que se ejecuten juntas o no se ejecute ninguna, agrúpalas en una transacción:

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;

La segunda consulta eliminará todos los alojamientos sin cocina, incluidos los que nunca se han reservado.

En una subconsulta no se puede leer la misma tabla de la que elimina DELETE. Por ejemplo, así no podrás eliminar las reservas más baratas que el precio medio:

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

La consulta terminará con un error:

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

Puedes sortear esta limitación envolviendo la subconsulta en otro SELECT. Así, el precio medio se calcula primero y se guarda en una tabla temporal, y la eliminación lee ya de ella:

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

Ponte a prueba

Hay que eliminar de la tabla Reservations solo las reservas que cuestan menos de 100. ¿Qué consulta lo consigue?