SQL DELETE : supprimer des lignes d'une table

Q: Quelle est la différence entre DELETE et TRUNCATE ?

DELETE supprime les lignes une par une et peut utiliser WHERE ; TRUNCATE supprime toutes les lignes d'un coup, est beaucoup plus rapide et réinitialise le compteur d'auto-incrément.

Leçon 8.3 · Temps de lecture : ~6 min · Exercices pratiques ↓

L'instruction SQL DELETE supprime des lignes d'une table. Dans cette leçon, vous apprendrez la syntaxe DELETE ... WHERE, comment supprimer des lignes selon une condition sur une autre table, pourquoi les clés étrangères bloquent parfois une suppression, comment vérifier un DELETE avant de l'exécuter et en quoi il diffère de TRUNCATE.

DELETE est la dernière des trois instructions de modification des données. C'est la plus simple à écrire et la plus dangereuse : une seule condition oubliée supprime toutes les lignes d'une table. Les règles de cette leçon visent à supprimer exactement les lignes que vous voulez.

Les bases d'exemple de SQLtest sont en lecture seule, ces instructions y renvoient donc une erreur de permission. Les exemples utilisent le schéma Sakila.

SQL DELETE : WHERE choisit les lignes à supprimer, la structure de la table reste, et une clé étrangère bloque la suppression des lignes référencées par d'autres tables

Syntaxe de DELETE

DELETE FROM table_name
WHERE condition;

DELETE FROM nomme la table.
WHERE choisit les lignes à supprimer. Sans WHERE, toutes les lignes sont supprimées.
Seules les lignes sont supprimées : la table, ses colonnes et ses index restent.

Supprimer des lignes selon une condition

DELETE FROM payment
WHERE payment_date < '2005-05-25';

Résultat : tous les paiements effectués avant le 25 mai 2005 sont supprimés.

Supprimer par la clé primaire supprime exactement une ligne :

DELETE FROM payment
WHERE payment_id = 16049;

Supprimer selon une condition sur une autre table

Une sous-requête dans WHERE choisit les lignes d'après des données d'autres tables. Supprimons les paiements des clients inactifs :

DELETE FROM payment
WHERE customer_id IN (
    SELECT customer_id
    FROM customer
    WHERE active = 0
);

Résultat : tous les paiements des 15 clients inactifs sont supprimés.

Comme pour UPDATE, MySQL et MariaDB n'autorisent pas la sous-requête à lire la table dont on supprime. MySQL prend aussi en charge une forme multi-tables : DELETE p FROM payment AS p INNER JOIN customer AS c ON c.customer_id = p.customer_id WHERE c.active = 0;.

Les clés étrangères peuvent bloquer un DELETE

D'autres tables référencent les clients : rental et payment stockent customer_id. Dans Sakila, ces clés étrangères sont définies avec ON DELETE RESTRICT, la base de données refuse donc de supprimer un client qui a encore des locations ou des paiements :

DELETE FROM customer
WHERE customer_id = 1;

Résultat : une erreur « Cannot delete or update a parent row: a foreign key constraint fails ». Le client reste.

Cela protège les données : sans cela, des locations pointeraient vers un client qui n'existe plus. Pour supprimer un tel client, il faut d'abord supprimer ou réaffecter les lignes dépendantes, ou bien la clé étrangère doit être définie avec ON DELETE CASCADE, qui les supprime automatiquement. Vous verrez les options des clés étrangères avec CREATE TABLE dans la leçon 9.1.

Vérifier avant de supprimer

Exécutez un SELECT avec le même WHERE et examinez les lignes.
Exécutez le DELETE et comparez le nombre de lignes affectées.

-- 1. Quelles lignes vont être supprimées ?
SELECT payment_id, customer_id, amount, payment_date
FROM payment
WHERE payment_date < '2005-05-25';

-- 2. Supprimer exactement ces lignes
DELETE FROM payment
WHERE payment_date < '2005-05-25';

En production, exécutez les suppressions importantes dans une transaction et assurez-vous d'avoir une sauvegarde. Beaucoup de systèmes ne suppriment jamais les données métier : ils marquent les lignes comme supprimées (active = 0, deleted_at), ce qu'on appelle une suppression logique (soft delete).

DELETE sans WHERE et TRUNCATE

DELETE FROM table_name; supprime toutes les lignes mais garde la table. TRUNCATE TABLE table_name; fait la même chose beaucoup plus vite, mais fonctionne différemment :

	`DELETE`	`TRUNCATE`
Peut ne supprimer qu'une partie des lignes	Oui, avec `WHERE`	Non, toujours toutes
Vitesse sur de grandes tables	Plus lent, ligne par ligne	Rapide
Réinitialise l'auto-incrément	Non	Oui
Bloqué par les clés étrangères	Seulement pour les lignes référencées	Si une table quelconque référence celle-ci

Vous verrez TRUNCATE et DROP TABLE dans la leçon 9.3.

Points clés :

DELETE FROM table WHERE condition supprime les lignes correspondantes ; la table reste.
Sans WHERE, toutes les lignes sont supprimées.
Une sous-requête dans WHERE permet de supprimer selon des conditions sur d'autres tables.
Les clés étrangères avec RESTRICT bloquent la suppression des lignes encore référencées.
Vérifiez d'abord avec SELECT ; utilisez transactions et sauvegardes pour les suppressions importantes.
Pour vider rapidement toute une table, utilisez TRUNCATE.

Questions fréquentes

Que se passe-t-il si j'exécute DELETE sans WHERE ?

Toutes les lignes de la table sont supprimées ; la table vide reste. Sauf si l'instruction a été exécutée dans une transaction que vous pouvez annuler, les données ne peuvent être restaurées qu'à partir d'une sauvegarde.

Pourquoi ne puis-je pas supprimer une ligne en SQL ?

Généralement à cause d'une clé étrangère : une autre table référence encore la ligne. Supprimez ou modifiez d'abord les lignes dépendantes, ou définissez la clé étrangère avec ON DELETE CASCADE.

Quelle est la différence entre DELETE et TRUNCATE ?

DELETE supprime les lignes une par une et peut utiliser WHERE ; TRUNCATE supprime toutes les lignes d'un coup, est beaucoup plus rapide et réinitialise le compteur d'auto-incrément.