How to delete duplicate records in mysql database?

What's the best way to delete duplicate records in a mysql database using rails or mysql queries?

Solution 1:

What you can do is copy the distinct records into a new table by:

 select distinct * into NewTable from MyTable

Solution 2:

Here's another idea in no particular language:

rs = `select a, b, count(*) as c from entries group by 1, 2 having c > 1`
rs.each do |a, b, c|
  `delete from entries where a=#{a} and b=#{b} limit #{c - 1}`
end

Edit:

Kudos to Olaf for that "having" hint :)

How to delete duplicate records in mysql database?

Solution 1:

Solution 2:

Related

Recent Posts