> -----Original Message----- > From: Gary Aitken [mailto:my...@dreamchaser.org] > 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`; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql