> -----Original Message-----
> From: Gary Aitken [mailto:[email protected]]
> 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
