Suppression de tout sauf un doublon dans une table

Publié le par

Pour un site j’ai implémenté une table pour faire lien entre l’id d’un profil et celui des utilisateurs qui visitent ce profil.

id int(10) UNSIGNED Non Aucun AUTO_INCREMENT
profile_id int(10) UNSIGNED Non 0
visitor_id int(10) UNSIGNED Non 0
date_visited timestamp Non CURRENT_TIMESTAMP

M’étant limité à une requête qui créé une paire ou qui mets à jour un timestamp si l’enregistrement existe, je me suis rendu compte que celle-ci ne se comportait pas comme prévu et produisait des doublons. En attendant de corriger celle-ci, il me fallait donc trouver une solution pour nettoyer la table en enlevant tout sauf un doublon pour chaque paire.

La première solution consisterais à utiliser cette requête:

delete from my_tab where id not in  
(select min(id) from my_tab group by profile_id, visitor_id);

Celle-ci ne fonctionnera pas tel quel et donnera une erreur « Can’t specify target in FROM clause » car on ne peut effecteur un select et modifier le contenu d’une table en même temps. L’astuce consiste donc à utiliser une table temporaire ce qui est possible en utilisant le mot clé AS:

delete from `my_tab` where id not in
( SELECT * FROM  
    (select min(id) from `my_tab` group by profile_id, visitor_id) AS temp_tab
)

– James Poulson.

Note du 23/06/2011: La clé primaire de la table peut être supprimée en faveur d’une clé composite portant sur profile_id et visitor_id.

2 réflexions au sujet de « Suppression de tout sauf un doublon dans une table »

  1. Salut Jérôme,

    Oui par la suite j’ai pensé à mettre une contraintre d’unicité sur le couple d’identifiants et il me reste à modifier la table.

    Côté requête j’avais utilisé un UPDATE en testant le retour de mysql_affected_rows(). Je ne sais pas encore ce qui a créé les doublons. Pour le duplicate entry il serait possible le combiner un INSERT et UPDATE de la manière suivante.

    INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

    Il y aurait aussi la possibilité d’utiliser un

    INSERT INTO ... SELECT ...

    mais je ne sais pas si ça convient dans ce cas précis.

  2. Pour ton problème d’insertion de doublon, une bonne pratique est d’utiliser la contrainte d’unicité.
    Je ne sais pas si tu y as déjà réfléchis, mais le principe est simpliste.

    Tu commences pas créer ta contrainte. Comme tu comptes une visite par personne sur un profil, il suffit de créer un index unique sur le couple profile_id et visitor_id.

    &nbsp;<br />
    &nbsp;   id int(10) UNSIGNED Non Aucun AUTO_INCREMENT&nbsp;<br />
    &nbsp;   profile_id int(10) UNSIGNED Non 0&nbsp;<br />
    &nbsp;   visitor_id int(10) UNSIGNED Non 0&nbsp;<br />
    &nbsp;   date_visited timestamp Non CURRENT_TIMESTAMP,&nbsp;<br />
    &nbsp;   UNIQUE(profile_id, visitor_id)&nbsp;<br />

    Ensuite, dans ton code, tu commences toujours par insérer une donner.
    À l’ancienne, ça donne :

    &nbsp;<br />
    mysql_query("INSERT INTO ...");&nbsp;<br />

    Si la requête échoue, alors tu testes la chaîne d’erreur, si tu trouves un « Duplicate entry », c’est que la donnée existe déjà, tu n’as plus qu’à faire ton update.

    Je dis « bonne pratique », mais bon, il y a d’autre moyen de tester. Tu peux toujours commencer par UPDATE, si aucune ligne modifiée, alors elle n’existe pas. Tu l’insères donc (toujours en testant le retour, au cas où).

Les commentaires sont fermés.