Search code examples
mysqlduplicatesno-duplicates

deleting duplicate records on mysql?

I have this mysql query that finds duplicates and the number of occurances for each topic:

SELECT name, 
 COUNT(name) AS NumOccurrences
FROM topics
GROUP BY name
HAVING ( COUNT(name) > 1 )

but what I want to do is delete all the duplicates that are found. I only want one unique name for each topic, and no duplicates!! thanks

Solution 
  • DELETE  t2
FROM    topics t1
JOIN    topics t2
ON      t2.name = t1.name
        AND t2.id < t1.id