Discussion :

[sinail]

Bonjour,

Je souhaite avoir une requête pour supprimé les doublons dans un base de données en gardant 1 ligne.

J'ai trouvé cette requête pour trouvé les doublons dans ma base mais impossible de trouvé une requêtes pour les supprimé.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT COUNT( * ) AS nbr_doublon, champ1, champ2, champ3 
FROM ma_table
GROUP BY champ1, champ2, champ3 
HAVING COUNT( * ) >1

Si vous pouvez m'aidé

Merci d'avance.

[bstevy]

Le meilleur moyen, c'est de créer une nouvelle table sans doublon.

Vous créez une table de structure identique dans laquelle vous transferez toutes vos données sans doublon.
Et vous intervertissez vos tables (soit par un rename si c'est dispo en mysql, soit en vidant la table et en y insérant une deuxième fois les données... mais ca fait deux insert, c'est moche)

selectionner les lignes sans doublon, à partir de votre requete, il faut juste changer le having en :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

HAVING COUNT( * ) = 1

Si vous voulez absolument passer par un delete (plus couteux qu'un insert) il faut faire un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

delete where exists (requete d'identification des doublons)

[sinail]

Merci pour ton aide, j'ai essayé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
delete where exists (
SELECT COUNT( * ) AS nbr_doublon, champ1, champ2, champ3 
FROM ma_table
GROUP BY champ1, champ2, champ3 
HAVING COUNT( * ) >1)

Mais cela ne fonctionne pas.

Le meilleur moyen, c'est de créer une nouvelle table sans doublon.

Vous créez une table de structure identique dans laquelle vous transferez toutes vos données sans doublon.
Et vous intervertissez vos tables (soit par un rename si c'est dispo en mysql, soit en vidant la table et en y insérant une deuxième fois les données... mais ca fait deux insert, c'est moche)

selectionner les lignes sans doublon, à partir de votre requete, il faut juste changer le having en :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

HAVING COUNT( * ) = 1

Si vous voulez absolument passer par un delete (plus couteux qu'un insert) il faut faire un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

delete where exists (requete d'identification des doublons)

[bstevy]

Et avec la bonne syntaxe ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
delete FROM ma_table 
where exists (
SELECT COUNT( * ) AS nbr_doublon, champ1, champ2, champ3 
FROM ma_table
where 'condition de jointure'
GROUP BY champ1, champ2, champ3 
HAVING COUNT( * ) >1)

[Artemus24]

Salut sinail.

Voici le résultat avec une table temporaire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create temporary table test ENGINE=MEMORY
as
(	select *
	from
	(	select *
		from doublon
		group by tri, val
		having count(*) = 1
	union
		select *
		from doublon
		group by tri, val
		having count(*) > 1
	) as x
);

Dans le cas du count(*) > 1, c'est la première occurrence qui a été sélectionnée.

@+

[bstevy]

Dans le cas du count(*) > 1, c'est la première occurrence qui a été sélectionnée.

C'est normal. MySQL permet ce genre d'écriture, mais elle n'est pas correct.
Un group by ne s'écrit pas comme cela.

[Artemus24]

Et qu'est-ce que tu préconises comme solution ?

[sinail]

Et avec la bonne syntaxe ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
delete FROM ma_table 
where exists (
SELECT COUNT( * ) AS nbr_doublon, champ1, champ2, champ3 
FROM ma_table
where 'condition de jointure'
GROUP BY champ1, champ2, champ3 
HAVING COUNT( * ) >1)

Bonjour, merci de ton aide mais qu'est ce que la condition de jointure ?

[Artemus24]

Salut sinail.

Bonjour, merci de ton aide mais qu'est ce que la condition de jointure ?

Une jointure, c'est quand tu as deux tables. Or ici, tu n'en as qu'une ! Donc pas de conditions de jointure.

La solution proposé par bstevy, ne peux pas fonctionner car tu va te retrouver avec l'erreur suivante :
Error 1093 (HY000) at line 41 : you can't specify target table 'doublon' for update in from clause
Ce message spécifie que tu ne peux pas utiliser la même table dans le sous-select que celle spécifié dans le delete.

Pour ce faire, il faut passer par une table intermédiaire, ou une table temporaire comme je l'ai indiqué dans mon dernier message, puis ensuite, tu fais la suppression comme ci-après :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
delete from doublon
where id not in (
select id
from test
);

Doublon, c'est la table qui contient tous les doublons, donc ta table d'origine.
Test, c'est la table temporaire que j'ai indiqué dans mon dernier message.
Tu supprimes toutes les lignes qui sont absentes de la table test, autrement dit, tous les doublons.

Avant de faire quoi que ce soit, vérifie que le résultat dans test est bien ce que tu attends.

@+

[sinail]

Bonjour, merci de ton aide mais qu'est ce que la condition de jointure ?

Merci pour ton aide mais cela ne fonctionne toujours pas

[sinail]

Bonjour, merci de ton aide mais qu'est ce que la condition de jointure ?

Bonjour merci pour ton aide mais j'ai une erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
DELETE FROM Client
WHERE EXISTS (
SELECT COUNT( * ) AS nbr_doublon, `nom`, `prenom`, `age`
FROM Client
GROUP BY `nom`, `prenom`, `age`  
HAVING COUNT( * ) >1)

#1093 - You can't specify target table 'Client' for update in FROM clause

[Artemus24]

Salut sinail.

J'ai déjà répondu au message d'erreur #1093.
Tu ne peux pas utiliser un sub-select qui fait référence à la même table que celle qui est utilisé dans le delete.

Pour ce faire, tu dois passer par une table intermédiaire ('test') pour obtenir les lignes qui sont susceptible à être supprimer.
==> voir message du 16/06/2015, 13h03

Et ensuite, tu fait ton delete en utilisant cette table ('test') afin de faire les suppressions des doublons.
==> 20/06/2015, 11h50

@+

[escartefigue]

Sans compter que les requetes proposées plus haut vont supprimer le doublon mais aussi l'original

Mauvaise solution donc

une solution possible est de créer une table de travail avec une clef unique et de la charger depuis celle qui comporte les doublons, les dupkeys seront naturellement éliminées

[Antoun]

1) On peut éviter (syntaxiquement) de passer par une table temporaire en ajoutant un niveau de sous-requête...
2) Pour conserver l'original, on peut s'appuyer sur l'ID pour dire qu'on ne supprime que les ID supérieures à la plus faible des lignes en doublons...

Illustration :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
DELETE FROM Client
WHERE EXISTS (
SELECT nom, prenom, age
FROM (SELECT * FROM Client C3) AS C2 -- détour pour contourner le problème de la table mutante 
WHERE C2.nom = Client.nom AND C2.prenom = Client.prenom and C2.age = Client.age and C2.id < Client.id)

Explication du 1) : quand il y a une sous-requête dans le FROM, MySQL l'implémente par une table temporaire ; du coup la lecture et la modification se font bien sur deux tables différentes. On évite donc de surcharger la syntaxe avec du DDL superflu, mais en réalité il y a bien une table temporaire qui est créée.

[Artemus24]

Salut à tous.

Sans compter que les requêtes proposées plus haut vont supprimer le doublon mais aussi l'original

Je constitue dans une table temporaire, exactement ce que je désire obtenir.
Si j'ai procéder ainsi, c'est juste pour vérifier que le résultat correspond exactement à ce que j'attendais, avant de faire la suppression.

J'ai décomposé en deux étapes afin de montrer que je récupère uniquement ce dont j'ai besoin.
a) j'ai extrait uniquement les lignes qui n'ont pas de doublons --> "having(count(*) = 1".
b) j'ai extrait les lignes qui ont des doublons en conservant uniquement la première occurrence --> "having(count(*) > 1".
Et je fusionne ces deux étapes. Les doublons seront supprimés dans la table 'test' !

Quand je fais la suppression, je fais un "where id not in (" afin de supprimer tous les 'id' qui sont en trop, c'est-à-dire les doublons.
Donc non, je n'ai pas supprimé l'original comme tu le prétends !
Et j'ai testé mon exemple avant de fournir la solution, afin de m'assurer que c'est correct et que cela correspond à la demande de sinail .

@ Antoun : je suppose que tu n'as testé ton exemple. J'ai vérifié sur un exemple et cela ne fonctionne pas.

On évite donc de surcharger la syntaxe avec du DDL superflu, mais en réalité il y a bien une table temporaire qui est créée.

D'accord, mais j'ai cru bien faire en décomposant la requête.
Qu'on externalise la table temporaire ou pas, au final, elle est quand même créée.

@ tous : Je reprends mon exemple et je vous donne la solution condensée, puisque cela dérange que je décompose en deux étapes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
delete from doublon
where id not in (
select id from (
	select id
	from (
		select id
		from doublon
		group by tri, val
		) as x
	) y
);

J'ai testé et cela fonctionne !
Inutile de faire un where compliqué car c'est le 'group by' qui se charge de supprimer les doublons.
Les doublons sont sur le couple (tri ; val).

@+

[Antoun]

Pour éclairer un peu le début de cette discussion, je précise que mon WHERE correspond à ce que bstevy appelait la "condition de jointure", et qui serait mieux décrite ici comme "condition de corrélation" (jointure entre une sous-requête et une table de sa requête principale).

@ Antoun : je suppose que tu n'as testé ton exemple. J'ai vérifié sur un exemple et cela ne fonctionne pas.

@Artemus24 : Pour ma part, je suppose que tu as mal vérifié. Voici mon code complet, et le résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE client (
  id INT(11) NOT NULL AUTO_INCREMENT,
  nom VARCHAR(30) DEFAULT NULL,
  prenom VARCHAR(30) DEFAULT NULL,
  age INT(11) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB ;
 
INSERT INTO client(nom, prenom, age) VALUES ('Doublon', 'Dominique', 11) ;
INSERT INTO client(nom, prenom, age) VALUES ('Doublon', 'Dominique', 22) ;
INSERT INTO client(nom, prenom, age) VALUES ('Doublon', 'Denis', 11) ;
INSERT INTO client(nom, prenom, age) VALUES ('Doublonne', 'Dominique', 11) ;
INSERT INTO client(nom, prenom, age) VALUES ('Doublon', 'Dominique', 11) ;
 
DELETE FROM Client
WHERE EXISTS (
SELECT nom, prenom, age
FROM (SELECT * FROM Client C3) AS C2
WHERE C2.nom = Client.nom AND C2.prenom = Client.prenom and C2.age = Client.age and C2.id < Client.id) ;

Query OK, 1 row affected (0.00 sec)

mysql> select * from client ;
+----+-----------+-----------+------+
| id | nom | prenom | age |
+----+-----------+-----------+------+
| 1 | Doublon | Dominique | 11 |
| 2 | Doublon | Dominique | 22 |
| 3 | Doublon | Denis | 11 |
| 4 | Doublonne | Dominique | 11 |
+----+-----------+-----------+------+
4 rows in set (0.00 sec)

mysql>

Quant à ta solution, je suis d'accord, elle est logique et doit marcher. Par contre je constate que tu utilises l'astuce de la sous-requête dans le FROM, celle dont tu prétends qu'elle ne fonctionne pas

[sinail]

Salut sinail.

Voici le résultat avec une table temporaire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create temporary table test ENGINE=MEMORY
as
(	select *
	from
	(	select *
		from doublon
		group by tri, val
		having count(*) = 1
	union
		select *
		from doublon
		group by tri, val
		having count(*) > 1
	) as x
);

Dans le cas du count(*) > 1, c'est la première occurrence qui a été sélectionnée.

@+

Bonjour, merci pour ton aide mais avec cette requête j'obtient une erreur comme quoi la table est pleine.

[sinail]

Salut à tous.


Je constitue dans une table temporaire, exactement ce que je désire obtenir.
Si j'ai procéder ainsi, c'est juste pour vérifier que le résultat correspond exactement à ce que j'attendais, avant de faire la suppression.

J'ai décomposé en deux étapes afin de montrer que je récupère uniquement ce dont j'ai besoin.
a) j'ai extrait uniquement les lignes qui n'ont pas de doublons --> "having(count(*) = 1".
b) j'ai extrait les lignes qui ont des doublons en conservant uniquement la première occurrence --> "having(count(*) > 1".
Et je fusionne ces deux étapes. Les doublons seront supprimés dans la table 'test' !

Quand je fais la suppression, je fais un "where id not in (" afin de supprimer tous les 'id' qui sont en trop, c'est-à-dire les doublons.
Donc non, je n'ai pas supprimé l'original comme tu le prétends !
Et j'ai testé mon exemple avant de fournir la solution, afin de m'assurer que c'est correct et que cela correspond à la demande de sinail .

@ Antoun : je suppose que tu n'as testé ton exemple. J'ai vérifié sur un exemple et cela ne fonctionne pas.


D'accord, mais j'ai cru bien faire en décomposant la requête.
Qu'on externalise la table temporaire ou pas, au final, elle est quand même créée.

@ tous : Je reprends mon exemple et je vous donne la solution condensée, puisque cela dérange que je décompose en deux étapes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
delete from doublon
where id not in (
select id from (
	select id
	from (
		select id
		from doublon
		group by tri, val
		) as x
	) y
);

J'ai testé et cela fonctionne !
Inutile de faire un where compliqué car c'est le 'group by' qui se charge de supprimer les doublons.
Les doublons sont sur le couple (tri ; val).

@+

Merci pour ton aide j'essaie cette requete mais elle a l'air assez longue a executé en sachant que je 25 000 rows.

[Antoun]

sinail, deux points :

1) d'une part tu devrais essayer ma requête qui est plus simple

2) d'autre part tu ne peux pas te contenter de dire "ça ne marche pas" ; si tu veux qu'on puisse t'aider, il faut que tu nous donnes la requête telle que tu l'as adaptée à ton cas, et le message d'erreur exact.

[Artemus24]

Salut à Antoun.

Je suis désolé si je t'ai froissé Antoun, car je suis un peu trop direct dans ma façon de m'exprimer.
Mais sache que ce n'est qu'une façon de parler et non une animosité à ton égard, ni envers personne d'autre.

J'ai repris ton exemple et je l'ai testé à nouveau sur ton exemple et OUI cela fonctionne.
Cette façon décrire ton DELETE, d'une part, je ne la connaissais pas et d'autre part n'est pas très performante, enfin, c'est mon avis. Voici le résultat de l'explain :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
explain delete from client
where exists (
        SELECT nom, prenom, age
        FROM (select * from client as c3) AS C2
        WHERE C2.nom    = client.nom
        AND   C2.prenom = client.prenom
        AND   C2.age    = client.age
        AND   C2.id     < client.id
)
--------------
 
+----+--------------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | client     | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
|  2 | DEPENDENT SUBQUERY | <derived3> | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
|  3 | DERIVED            | c3         | ALL  | NULL          | NULL | NULL    | NULL |    7 | NULL        |
+----+--------------------+------------+------+---------------+------+---------+------+------+-------------+

Il y a trois niveaux de traitement. Et voici ma façon d'écrire le même genre de DELETE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
explain delete C1 from client as C1
inner     join client as C2
on     C2.nom    = C1.nom
and    C2.prenom = C1.prenom
and    C2.age    = C1.age
and    C2.id     < C1.id
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | C1    | ALL  | PRIMARY       | NULL | NULL    | NULL |    7 | NULL                                           |
|  1 | SIMPLE      | C2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    7 | Range checked for each record (index map: 0x1) |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

Ici, on a plus que deux niveaux de traitement. J'ai vérifié et j'ai bien le même résultat !

Même si j'ai une bonne connaissance de l'administration des bases de données, être confronté au problèmes des autres est une source d'enrichissement. D'où les interventions.

Quant à ta solution, je suis d'accord, elle est logique et doit marcher. Par contre je constate que tu utilises l'astuce de la sous-requête dans le FROM, celle dont tu prétends qu'elle ne fonctionne pas.

Je connaissais l'astuce en question, mais le problème est plus dans mes habitudes de travail.
Pour faire une suppression, il est vrai que j'ai tendance à passer par la création d'une table intermédiaire. Pourquoi ?
Car je fais cela en ligne de commandes et avant de lancer la suppression, je m'assure que j'ai bien le résultat de ce que je désire faire.

Et pourquoi procéder ainsi ? En tant qu'administrateur, il ne nous est pas permis de monopoliser des ressources qui viendraient pénaliser les utilisateurs.
Ainsi je crée une table, copie de celle qui est en production. Je fais ma manipulation et si tout est conforme, je procède au basculement quand les ressources ne sont plus utilisées par les utilisateurs.

@+