Comment supprimer des doublons dans une table SQL basée sur plusieurs champs
j'ai une table de jeux, qui est décrite comme suit:
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| date | date | NO | | NULL | |
| time | time | NO | | NULL | |
| hometeam_id | int(11) | NO | MUL | NULL | |
| awayteam_id | int(11) | NO | MUL | NULL | |
| locationcity | varchar(30) | NO | | NULL | |
| locationstate | varchar(20) | NO | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
mais chaque jeu a une entrée en double dans la table quelque part, parce que chaque jeu était dans les horaires pour deux équipes. Y a-t-il une instruction sql que je puisse utiliser pour regarder et supprimer tous les doublons basés sur les mêmes champs date, time, hometeam_id, awayteam_id, locationcity et locationstate?
8 réponses
Vous devriez être en mesure de faire une sous-requête corrélée à supprimer les données. Trouvez toutes les lignes qui sont des doublons et supprimez toutes sauf celle avec le plus petit id. Pour MYSQL, une jointure interne (équivalent fonctionnel de EXISTS) doit être utilisée, comme suit:
delete games from games inner join
(select min(id) minid, date, time,
hometeam_id, awayteam_id, locationcity, locationstate
from games
group by date, time, hometeam_id,
awayteam_id, locationcity, locationstate
having count(1) > 1) as duplicates
on (duplicates.date = games.date
and duplicates.time = games.time
and duplicates.hometeam_id = games.hometeam_id
and duplicates.awayteam_id = games.awayteam_id
and duplicates.locationcity = games.locationcity
and duplicates.locationstate = games.locationstate
and duplicates.minid <> games.id)
pour tester, remplacer delete games from gamesselect * from games. Ne vous contentez pas d'exécuter un delete sur votre DB :-)
Vous pouvez essayer une telle requête:
DELETE FROM table_name AS t1
WHERE EXISTS (
SELECT 1 FROM table_name AS t2
WHERE t2.date = t1.date
AND t2.time = t1.time
AND t2.hometeam_id = t1.hometeam_id
AND t2.awayteam_id = t1.awayteam_id
AND t2.locationcity = t1.locationcity
AND t2.id > t1.id )
cela ne laissera dans la base de données qu'un seul exemple de chaque instance de jeu ayant le plus petit id.
La meilleure chose qui a fonctionné pour moi était de recréer la table.
CREATE TABLE newtable SELECT * FROM oldtable GROUP BY field1,field2;
Vous pouvez alors renommer.
pour obtenir la liste des doublons qui correspondent à deux champs
select t.ID, t.field1, t.field2
from (
select field1, field2
from table_name
group by field1, field2
having count(*) > 1) x, table_name t
where x.field1 = t.field1 and x.field2 = t.field2
order by t.field1, t.field2
Et pour supprimer tous les doublons seulement
DELETE x
FROM table_name x
JOIN table_name y
ON y.field1= x.field1
AND y.field2 = x.field2
AND y.id < x.id;
select orig.id,
dupl.id
from games orig,
games dupl
where orig.date = dupl.date
and orig.time = dupl.time
and orig.hometeam_id = dupl.hometeam_id
and orig. awayteam_id = dupl.awayeam_id
and orig.locationcity = dupl.locationcity
and orig.locationstate = dupl.locationstate
and orig.id < dupl.id
cela devrait vous donner les doublons; vous pouvez l'utiliser comme une sous-requête pour spécifier l'Id à supprimer.
tant que vous ne recevez pas l'id (clé primaire) de la table dans votre requête select et que les autres données sont exactement les mêmes, vous pouvez utiliser SELECT DISTINCT pour éviter d'obtenir des résultats en double.
delete from games
where id not in
(select max(id) from games
group by date, time, hometeam_id, awayteam_id, locationcity, locationstate
);
solution de contournement
select max(id) id from games
group by date, time, hometeam_id, awayteam_id, locationcity, locationstate
into table temp_table;
delete from games where id in (select id from temp);
DELETE FROM table
WHERE id =
(SELECT t.id
FROM table as t
JOIN (table as tj ON (t.date = tj.data
AND t.hometeam_id = tj.hometeam_id
AND t.awayteam_id = tj.awayteam_id
...))