deleting duplicate records on mysql?

Question

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

possible duplicate of [Deletion of duplicate records using one query only](http://stackoverflow.com/questions/5324563/deletion-of-duplicate-records-using-one-query-only) — Sachin Shanbhag, Mar 31 '11 at 11:11
@Sachin: that question is about a table with identical rows. This one is similar, it has primary key: http://stackoverflow.com/questions/4133687/delete-all-duplicate-topics-with-few-conditions — ypercubeᵀᴹ, Mar 31 '11 at 11:14

score 3 · Accepted Answer · answered Mar 31 '11 at 11:16

3

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

answered Mar 31 '11 at 11:16

Quassnoi

score 0 · Answer 2 · answered Mar 31 '11 at 11:11

0

I would copy all the unique entries to a new table:

CREATE TABLE new_table as
SELECT * FROM old_table WHERE 1 GROUP BY unique_column_name;

Check the data, then delete your old table when you're sure everything's good and rename the new table to the old one.

Then make the name column unique so you won't have to do this again.

Cheers

answered Mar 31 '11 at 11:11

Christof

It is not guaranteed that results in unaggregated columns will belong to same records. – Quassnoi Mar 31 '11 at 11:18

2 Answers2