Tutorial SQL: Eliminación de datos: DELETE

Sintaxis DELETE FROM

La sentencia DELETE es una de las operaciones fundamentales en SQL que permite eliminar registros de una tabla. Esta operación forma parte del lenguaje de manipulación de datos (DML) junto con INSERT y UPDATE, que ya hemos estudiado en lecciones anteriores.

La sintaxis básica del comando DELETE es sencilla pero potente, permitiéndonos eliminar desde un único registro hasta todos los registros de una tabla. Veamos su estructura:

DELETE FROM nombre_tabla
WHERE condición;

Los componentes principales de esta sentencia son:

• DELETE FROM: Indica la operación de eliminación y la tabla objetivo.
• WHERE: Cláusula opcional que especifica qué registros deben eliminarse.

Si omitimos la cláusula WHERE, la sentencia eliminará todos los registros de la tabla, manteniendo la estructura de la misma. Por ello, es crucial tener precaución al ejecutar sentencias DELETE.

Ejemplos básicos

Veamos algunos ejemplos prácticos para entender mejor cómo funciona:

Ejemplo 1: Eliminar un registro específico por su identificador único:

DELETE FROM clientes
WHERE id = 25;

Este comando elimina el cliente cuyo ID es 25.

Ejemplo 2: Eliminar múltiples registros que cumplan una condición:

DELETE FROM productos
WHERE precio < 10;

Esta sentencia elimina todos los productos con precio menor a 10.

Ejemplo 3: Eliminar todos los registros de una tabla:

DELETE FROM registros_temporales;

Al omitir la cláusula WHERE, se eliminan todos los registros de la tabla "registros_temporales".

Uso de operadores en la cláusula WHERE

Podemos utilizar diversos operadores en la cláusula WHERE para definir condiciones más específicas:

• Operadores de comparación (=, <>, <, >, <=, >=):

DELETE FROM pedidos
WHERE fecha_entrega < '2023-01-01';

• Operadores lógicos (AND, OR, NOT):

DELETE FROM empleados
WHERE departamento = 'Ventas' AND fecha_contratacion < '2020-01-01';

• Operador LIKE para patrones de texto:

DELETE FROM mensajes
WHERE asunto LIKE '%spam%';

• Operador IN para múltiples valores posibles:

DELETE FROM productos
WHERE categoria_id IN (3, 7, 9);

• Operador BETWEEN para rangos:

DELETE FROM transacciones
WHERE fecha BETWEEN '2023-01-01' AND '2023-01-31';

DELETE con subconsultas

También podemos utilizar subconsultas en la cláusula WHERE para crear condiciones más complejas:

DELETE FROM productos
WHERE proveedor_id IN (
    SELECT id 
    FROM proveedores 
    WHERE pais = 'Italia'
);

Este ejemplo elimina todos los productos cuyos proveedores son de Italia.

Consideraciones importantes

Al trabajar con DELETE, es importante tener en cuenta:

1. Irreversibilidad: Una vez ejecutado, DELETE no se puede deshacer fácilmente sin una copia de seguridad.

2. Restricciones de integridad referencial: Si existen claves foráneas que hacen referencia a los registros que intentamos eliminar, la operación puede fallar a menos que se hayan configurado acciones en cascada.

3. Rendimiento: Las operaciones DELETE que afectan a muchos registros pueden ser costosas en términos de rendimiento, especialmente en tablas grandes.

4. Buenas prácticas:

• Siempre hacer una copia de seguridad antes de ejecutar DELETE en datos importantes.
• Probar primero con un SELECT para verificar qué registros serán afectados.
• Considerar el uso de transacciones para operaciones complejas.

-- Verificar primero qué registros se eliminarán
SELECT * FROM clientes WHERE ultima_compra < '2020-01-01';

-- Si el resultado es correcto, proceder con el DELETE
DELETE FROM clientes WHERE ultima_compra < '2020-01-01';

Diferencias entre MySQL y PostgreSQL

Aunque la sintaxis básica de DELETE es similar en MySQL y PostgreSQL, existen algunas diferencias sutiles:

• MySQL permite una sintaxis alternativa para eliminar registros de múltiples tablas en una sola sentencia:

DELETE t1, t2 
FROM tabla1 t1
JOIN tabla2 t2 ON t1.id = t2.tabla1_id
WHERE t1.fecha < '2023-01-01';

• PostgreSQL utiliza una sintaxis diferente para eliminar con joins:

DELETE FROM tabla1
USING tabla2
WHERE tabla1.id = tabla2.tabla1_id AND tabla2.activo = false;

La comprensión adecuada de la sintaxis DELETE FROM es esencial para mantener la integridad y calidad de los datos en nuestras bases de datos, permitiéndonos eliminar información obsoleta o incorrecta de manera controlada y precisa.

Condiciones de eliminación

Las condiciones de eliminación en SQL son el mecanismo principal para determinar qué registros serán eliminados al ejecutar una sentencia DELETE. Estas condiciones, especificadas en la cláusula WHERE, actúan como un filtro que permite realizar eliminaciones selectivas y precisas en lugar de borrar todos los datos de una tabla.

Importancia de las condiciones bien definidas

Definir condiciones de eliminación adecuadas es crucial por varias razones:

• Previenen la eliminación accidental de datos importantes
• Permiten operaciones de limpieza específicas y controladas
• Mejoran el rendimiento al limitar el alcance de la operación
• Mantienen la integridad de los datos relacionados

Tipos de condiciones para DELETE

Podemos clasificar las condiciones de eliminación según su complejidad y propósito:

Condiciones simples

Las condiciones simples utilizan un solo criterio para filtrar los registros:

DELETE FROM usuarios
WHERE estado = 'inactivo';

DELETE FROM productos
WHERE fecha_caducidad < CURRENT_DATE;

Condiciones compuestas

Combinan múltiples criterios mediante operadores lógicos (AND, OR, NOT):

DELETE FROM pedidos
WHERE (estado = 'cancelado' AND fecha < '2023-01-01')
   OR (estado = 'pendiente' AND fecha < '2022-06-01');

Este tipo de condiciones permite crear filtros más precisos para situaciones complejas.

Condiciones basadas en relaciones

Utilizan la relación entre tablas para determinar qué registros eliminar:

DELETE FROM detalles_pedido
WHERE pedido_id IN (
    SELECT id FROM pedidos
    WHERE cliente_id = 1045 AND estado = 'cancelado'
);

Estrategias para condiciones de eliminación seguras

Verificación previa con SELECT

Una práctica recomendada es verificar primero qué registros serán afectados utilizando la misma condición en una consulta SELECT:

-- Primero verificamos
SELECT * FROM clientes 
WHERE ultima_visita < '2022-01-01' AND tipo = 'prueba';

-- Si el resultado es correcto, procedemos con la eliminación
DELETE FROM clientes 
WHERE ultima_visita < '2022-01-01' AND tipo = 'prueba';

Uso de LIMIT en MySQL

MySQL permite limitar el número de filas afectadas por una operación DELETE, lo que puede ser útil para operaciones graduales en tablas grandes:

DELETE FROM logs_sistema
WHERE fecha < '2023-01-01'
ORDER BY fecha
LIMIT 1000;

Esta técnica es especialmente útil cuando necesitamos eliminar grandes volúmenes de datos sin bloquear la tabla por períodos prolongados.

Eliminación por lotes en PostgreSQL

PostgreSQL no soporta LIMIT en DELETE directamente, pero podemos lograr un efecto similar usando subconsultas:

DELETE FROM logs_sistema
WHERE id IN (
    SELECT id FROM logs_sistema
    WHERE fecha < '2023-01-01'
    ORDER BY fecha
    LIMIT 1000
);

Condiciones con funciones

Las funciones pueden enriquecer nuestras condiciones de eliminación, permitiendo operaciones más sofisticadas:

Funciones de fecha

-- Eliminar registros de hace más de un año
DELETE FROM estadisticas_temporales
WHERE fecha < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);  -- MySQL

-- Equivalente en PostgreSQL
DELETE FROM estadisticas_temporales
WHERE fecha < CURRENT_DATE - INTERVAL '1 year';

Funciones de texto

-- Eliminar usuarios con dominios de correo específicos
DELETE FROM usuarios
WHERE LOWER(email) LIKE '%@dominio-temporal.com';

Funciones matemáticas y agregadas

-- Eliminar productos con bajo rendimiento de ventas
DELETE FROM productos
WHERE id IN (
    SELECT producto_id
    FROM ventas
    GROUP BY producto_id
    HAVING SUM(cantidad) < 10
);

Condiciones para casos especiales

Eliminación basada en duplicados

Para eliminar registros duplicados, manteniendo solo una instancia:

-- En MySQL
DELETE t1 FROM contactos t1
INNER JOIN contactos t2
WHERE t1.id > t2.id AND t1.email = t2.email;

-- En PostgreSQL
DELETE FROM contactos
WHERE id IN (
    SELECT id FROM (
        SELECT id, 
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
        FROM contactos
    ) t
    WHERE t.rn > 1
);

Eliminación condicional con CASE

Podemos usar expresiones CASE para crear condiciones más dinámicas:

DELETE FROM inventario
WHERE 
    CASE 
        WHEN categoria = 'perecedero' THEN fecha_caducidad < CURRENT_DATE
        WHEN categoria = 'electrónico' THEN fecha_ultima_venta < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR)
        ELSE false
    END;

Consideraciones de rendimiento

Las condiciones de eliminación también afectan el rendimiento de la operación:

• Índices: Las condiciones basadas en columnas indexadas se ejecutan más rápidamente
• Complejidad: Las condiciones muy complejas pueden ralentizar la ejecución
• Volumen: Eliminar grandes cantidades de datos puede afectar el rendimiento general

-- Más eficiente (si id está indexado)
DELETE FROM logs WHERE id < 1000;

-- Menos eficiente (si texto no está indexado)
DELETE FROM logs WHERE mensaje LIKE '%error%';

Condiciones de eliminación y transacciones

Para operaciones de eliminación críticas, es recomendable utilizar transacciones que permitan revertir los cambios en caso de error:

-- En MySQL y PostgreSQL
BEGIN;
DELETE FROM facturas WHERE cliente_id = 500;
DELETE FROM clientes WHERE id = 500;
COMMIT;

Si ocurre algún problema entre las dos operaciones DELETE, podemos usar ROLLBACK para deshacer los cambios y mantener la integridad de los datos.

Las condiciones de eliminación bien diseñadas son fundamentales para mantener la calidad e integridad de los datos, permitiendo operaciones de limpieza precisas y controladas que eliminan solo la información que realmente debe ser removida de nuestras bases de datos.

DELETE vs TRUNCATE

Cuando necesitamos eliminar datos de una tabla en SQL, disponemos de dos comandos principales: DELETE y TRUNCATE. Aunque ambos tienen el mismo objetivo final —eliminar registros—, funcionan de manera fundamentalmente diferente y cada uno resulta más adecuado en situaciones específicas.

Diferencias principales

La elección entre DELETE y TRUNCATE depende de varios factores clave:

• Alcance de la eliminación: DELETE puede eliminar registros específicos mediante condiciones, mientras que TRUNCATE siempre elimina todos los registros.

• Velocidad de ejecución: TRUNCATE es significativamente más rápido, especialmente en tablas grandes.

• Registro de operaciones: DELETE registra cada fila eliminada en el log de transacciones, TRUNCATE solo registra la liberación de las páginas de datos.

• Comportamiento con restricciones: DELETE respeta las restricciones de integridad referencial, TRUNCATE requiere que no existan referencias a la tabla.

Sintaxis comparativa

La sintaxis de ambos comandos refleja sus diferentes enfoques:

-- Sintaxis DELETE
DELETE FROM nombre_tabla
WHERE condición;

-- Sintaxis TRUNCATE
TRUNCATE TABLE nombre_tabla;

Observa que TRUNCATE no admite una cláusula WHERE, lo que refuerza su naturaleza de "todo o nada".

Funcionamiento interno

Para entender mejor las diferencias, es importante conocer cómo funciona cada comando internamente:

DELETE

• Elimina los registros uno por uno
• Mantiene un registro detallado en el log de transacciones
• Conserva el espacio asignado a la tabla
• Mantiene los valores de las secuencias o identity

-- Eliminar clientes inactivos
DELETE FROM clientes WHERE ultima_actividad < '2022-01-01';

-- Eliminar todos los registros (pero menos eficiente que TRUNCATE)
DELETE FROM registros_temporales;

TRUNCATE

• Elimina las páginas de datos completas en lugar de filas individuales
• Reinicia los contadores de identidad (AUTO_INCREMENT en MySQL, SERIAL en PostgreSQL)
• Libera el espacio de almacenamiento (excepto el reservado por la definición de la tabla)
• Requiere permisos más elevados en algunos sistemas

-- Vaciar completamente una tabla de logs
TRUNCATE TABLE logs_sistema;

-- En PostgreSQL se puede omitir la palabra TABLE
TRUNCATE logs_sistema;

Impacto en el rendimiento

La diferencia de rendimiento entre ambos comandos puede ser dramática:

• En una tabla con millones de registros, un DELETE sin condición podría tardar minutos, mientras que TRUNCATE completaría la operación en segundos.

• El impacto en el log de transacciones también es significativo:

-- Puede generar gigabytes de logs en tablas grandes
DELETE FROM tabla_grande;

-- Genera una entrada mínima en el log
TRUNCATE TABLE tabla_grande;

Comportamiento con transacciones

El comportamiento transaccional varía entre ambos comandos:

• DELETE es completamente transaccional:

BEGIN;
DELETE FROM pedidos WHERE cliente_id = 100;
-- Si algo falla, podemos hacer ROLLBACK
COMMIT;

• TRUNCATE tiene comportamiento variable según el sistema:
• En PostgreSQL es transaccional y se puede revertir
• En MySQL (InnoDB) también es transaccional desde versiones recientes
• En SQL Server implica un commit implícito de la transacción actual

-- En PostgreSQL
BEGIN;
TRUNCATE TABLE tabla_temporal;
-- Si algo falla, podemos hacer ROLLBACK
COMMIT;

Restricciones y limitaciones

Existen situaciones donde no podemos usar TRUNCATE:

• Cuando hay referencias de clave foránea: TRUNCATE fallará si existen restricciones de integridad referencial activas que apunten a la tabla.

-- Esto fallará si hay claves foráneas que referencian a clientes
TRUNCATE TABLE clientes;

-- Alternativa usando DELETE
DELETE FROM clientes;

• Cuando necesitamos condiciones: Si necesitamos eliminar solo ciertos registros, TRUNCATE no es una opción.

• Cuando hay triggers: En muchos sistemas, los triggers de DELETE no se activan con TRUNCATE.

Casos de uso recomendados

Usar DELETE cuando:

• Necesitamos eliminar registros específicos basados en condiciones
• Requerimos mantener la integridad referencial automáticamente
• Necesitamos que se ejecuten triggers asociados a la eliminación
• Queremos mantener los valores de secuencias o identity
• Necesitamos registrar detalladamente cada eliminación

-- Eliminar productos descontinuados
DELETE FROM productos WHERE estado = 'descontinuado';

-- Limpieza de datos específicos
DELETE FROM logs WHERE nivel = 'debug' AND fecha < CURRENT_DATE - INTERVAL '30 days';

Usar TRUNCATE cuando:

• Necesitamos eliminar todos los registros de una tabla
• Buscamos el máximo rendimiento en la operación
• Queremos reiniciar contadores de identidad
• La tabla es temporal o de staging sin referencias externas
• Estamos en una fase de desarrollo o pruebas

-- Vaciar tablas temporales al final del proceso
TRUNCATE TABLE tmp_resultados_proceso;
TRUNCATE TABLE tmp_estadisticas;

-- Reiniciar una tabla para cargar datos desde cero
TRUNCATE TABLE productos_importacion;

Diferencias específicas en MySQL y PostgreSQL

Aunque la funcionalidad básica es similar, existen algunas diferencias entre sistemas:

MySQL

• Permite TRUNCATE en tablas con particiones
• Soporta la sintaxis TRUNCATE nombre_tabla (sin la palabra TABLE)
• Con InnoDB, TRUNCATE es transaccional en versiones recientes

-- Sintaxis válida en MySQL
TRUNCATE logs_sistema;

PostgreSQL

• Permite truncar múltiples tablas en una sola sentencia
• Ofrece opciones adicionales como CASCADE y RESTART IDENTITY
• Es completamente transaccional

-- Truncar múltiples tablas relacionadas en PostgreSQL
TRUNCATE TABLE tabla1, tabla2, tabla3 CASCADE;

-- Reiniciar secuencias explícitamente
TRUNCATE TABLE productos RESTART IDENTITY;

Verificación previa a la eliminación

Independientemente del método elegido, es recomendable verificar los datos antes de eliminarlos:

-- Antes de DELETE, verificar qué se eliminará
SELECT COUNT(*) FROM logs WHERE fecha < '2023-01-01';

-- Antes de TRUNCATE, verificar el contenido de la tabla
SELECT COUNT(*) FROM tabla_temporal;

La elección entre DELETE y TRUNCATE debe basarse en los requisitos específicos de cada situación, considerando factores como el volumen de datos, la necesidad de condiciones, las restricciones de integridad y el rendimiento requerido. Ambos comandos son herramientas valiosas que, utilizadas correctamente, nos permiten mantener nuestras bases de datos optimizadas y libres de información innecesaria.