Supprimer les lignes en double dans MySQL

1. Supprimer les lignes en double à l’aide de l’instruction DELETE JOIN
2. Supprimer les lignes en double à l’aide d’une requête imbriquée
3. Supprimer les lignes en double à l’aide d’une table temporaire
4. Supprimer les lignes en double à l’aide de la fonction ROW_NUMBER()

Cet article vous montrera les nombreuses façons de supprimer les lignes en double présentes dans la table dans MySQL. Il existe quatre approches différentes pour accomplir cette tâche.

1. Supprimez les lignes en double à l’aide de l’instruction DELETE JOIN
2. Supprimer les lignes en double à l’aide de la requête imbriquée
3. Supprimer les lignes en double à l’aide d’une table temporaire
4. Supprimez les lignes en double à l’aide de la fonction ROW_NUMBER()

Le script suivant crée une table customers avec quatre colonnes (custid, first_name, last_name et email).

CREATE TABLE customers (custid INT,  first_name VARCHAR(50) NOT NULL,  last_name  VARCHAR(50) NOT NULL,  email VARCHAR(255) NOT NULL );
CREATE TABLE customers (custid INT,  first_name VARCHAR(50) NOT NULL,  last_name  VARCHAR(50) NOT NULL,  email VARCHAR(255) NOT NULL );

Insérez des exemples de valeurs de données dans la table customers pour la démonstration.

INSERT INTO customers  
VALUES (110,'Susane','Mathew','sussane.mathew@gmail.com'),
       (124,'Jean','Carl','jean.carl@gmail.com'),
       (331,'Peter','cohelo','peter.coh@google.com'),
       (114,'Jaine','Lora','jaine.l@abs.com'),
       (244,'Junas','sen','jonas.sen@mac.com');

INSERT INTO customers  
VALUES (113,'Jaine','Lora','jaine.l@abs.com'),
       (111,'Susane','Mathew','sussane.mathew@gmail.com'),
       (665,'Roma','Shetty','roma.sh11@yahoo.com'),
       (997,'Beatrice','shelon','beatrice.ss22@yahoo.com'),
       (332,'Peter','cohelo','peter.coh@google.com');

Remarque : Vous pouvez l’utiliser pour insérer à nouveau des valeurs après la suppression.

Voici la requête donnée qui renvoie toutes les données de la table customers :

SELECT * FROM customers order by custid;

Pour rechercher les enregistrements en double dans la table, nous allons exécuter la requête mentionnée ci-dessous dans la table customers.

SELECT custid, COUNT(custid) FROM customers GROUP BY  custid HAVING  COUNT(custid) > 1; 

Comme vous pouvez le voir, nous avons trois lignes avec un identifiant client en double dans nos résultats.

Supprimer les lignes en double à l’aide de l’instruction DELETE JOIN

L’utilisation de INNER JOIN avec la instruction Delete vous permet de supprimer les lignes en double de votre table dans MySQL.

La requête suivante utilise le concept de requête imbriquée en sélectionnant toutes les lignes qui ont des enregistrements en double avec l’ID client le plus bas. Une fois trouvés, nous supprimerons alors ces enregistrements en double avec le plus petit custid :

Delete from customers where custid IN (Select c1.custid FROM customers as c1
INNER JOIN customers as c2 ON c1.custid < c2.custid AND c1.email = c2.email);

La table client est référencée deux fois dans cette requête ; par conséquent, il utilise les alias c1 et c2.

La sortie sera :

Il indiquait que trois lignes avaient été supprimées.

SELECT custid, COUNT(custid) FROM customers GROUP BY  custid HAVING  COUNT(custid) > 1; 

Maintenant, cette requête renvoie un ensemble vide, ce qui signifie que les lignes en double ont été supprimées.

Nous pouvons vérifier les données de la table customers à l’aide de la requête sélectionner :

SELECT * FROM customers;

Si vous souhaitez supprimer les lignes en double et conserver le custid le plus bas, vous pouvez utiliser la même requête mais avec une légère variation dans la condition, comme indiqué dans l’instruction suivante :

Delete from customers where custid IN (Select c1.custid FROM customers as c1
INNER JOIN customers as c2 ON c1.custid > c2.custid AND c1.email = c2.email);

La sortie suivante affiche les données de la table customers après avoir supprimé les lignes en double.

Supprimer les lignes en double à l’aide d’une requête imbriquée

Voyons maintenant la procédure pas à pas pour supprimer les lignes en double à l’aide d’une requête imbriquée. Il s’agit d’une approche relativement simple pour résoudre le problème.

Tout d’abord, nous sélectionnerons des enregistrements uniques dans la table à l’aide de cette requête.

Select * from (select max(custid) from customers group by email);

Ensuite, nous utiliserons la requête delete avec la clause where, comme indiqué ci-dessous, pour supprimer les lignes en double dans la table.

Delete from customers where custid not in 
    (select * from (select max(custid) from customers group by email));

La sortie sera :

Supprimer les lignes en double à l’aide d’une table temporaire

Voyons maintenant la procédure étape par étape pour supprimer les lignes en double à l’aide d’une table temporaire :

1. Tout d’abord, vous devez créer une nouvelle table avec la même structure que la table d’origine.
2. Maintenant, insérez des lignes distinctes de la table d’origine dans la table temporaire.
3. Supprimez la table d’origine et renommez la table temporaire en table d’origine.

Étape 1 : Création du tableau à l’aide du mot-clé CREATE TABLE et LIKE

La syntaxe pour copier la structure entière de la table est comme indiqué ci-dessous.

CREATE TABLE destination_table LIKE source;

Donc, en supposant que nous ayons la même table client, nous écrirons la requête ci-dessous.

CREATE TABLE temporary LIKE customers;

Étape 2. Insertion de lignes dans une table temporaire

La requête ci-dessous copie la ligne unique des clients et l’écrit dans une table temporaire. Ici, nous regroupons par email.

INSERT INTO temporary SELECT * FROM customers GROUP BY email; 

Étape 3. Supprimez la table client d’origine et créez une table temporaire qui servira de table d’origine en la renommant clients.

DROP TABLE customers;

ALTER TABLE temporary RENAME TO customers;

La sortie sera :

Cette approche prend du temps car elle nécessite une modification de la structure de la table plutôt que de simplement travailler sur les valeurs des données.

Supprimer les lignes en double à l’aide de la fonction ROW_NUMBER()

La fonction ROW_NUMBER() a été introduite dans MySQL version 8.02. Ainsi, vous pouvez opter pour cette approche si vous utilisez une version MySQL supérieure à 8.02.

Cette requête affecte une valeur numérique à chaque ligne à l’aide de la fonction ROW_NUMBER(). Dans le cas d’e-mails en double, le numéro de ligne sera supérieur à un.

SELECT  custid, email, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email ) AS row FROM customers;

L’extrait de code ci-dessus renvoie une liste d’identifiants des lignes en double :

SELECT custid
FROM ( SELECT custid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS row FROM customers) t WHERE row > 1;

Une fois que nous avons obtenu la liste des clients avec des valeurs en double, nous pouvons la supprimer à l’aide de l’instruction delete avec une sous-requête dans la clause where comme indiqué ci-dessous.

DELETE FROM customers
WHERE custid IN 
(SELECT custid  FROM 
     (SELECT custid, ROW_NUMBER() OVER
                (PARTITION BY email ORDER BY email) AS row FROM customers) t
                      WHERE row > 1);

La sortie sera :