List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:June 15 2012 11:58pm
Subject:RE: console input - finding duplicate entries
View as plain text  
> -----Original Message-----
> From: Gary Aitken [mailto:mysql@stripped]
> Sent: Thursday, June 14, 2012 2:58 PM
> 
> I can get the table loaded by specifying REPLACE INTO TABLE, but that
still
> leaves me with not knowing where the duplicate records are.

To find duplicate entries

select dupe_column, count(*) as n from mytable group by dupe_column having n
> 1;

or

select n, m, count(*) as c from foo group by n, m having c > 1;

Here's one solution that will find the oldest duplicate(s)

SELECT prod.prod_num,prod.prod_title,prod.updated FROM prod
JOIN (
     SELECT prod_title,max(updated) maxdate
     FROM prod GROUP BY prod_title 
     ) 
     AS proda
     ON prod.prod_title = proda.prod_title 
        AND 
        prod.updated != proda.maxdate

A simple and fast way is via an exclusion join

delete t1
from tbl t1
left join tbl t2 on t1.value=t2.value and t1.id>t2.id
where t2.id is not null;

Deleting duplicate rows via temporary table

DROP TABLE IF EXISTS `dupes`;
CREATE TEMPORARY TABLE dupes 
  SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) > 1 ORDER BY
BID;
ALTER TABLE `dupes` ADD INDEX `LogID` ( `LogID` ); #//This index is
critical.
DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes);
DROP TABLE IF EXISTS `dupes`;

Thread
RE: console input - finding duplicate entriesDaevid Vincent15 Jun