MySql - delete duplications from table

I have a table in my MySql server with the following columns: ID (int, key), type (int), name (varchar).

Due to an error in my application, duplicate entries was inserted to the d开发者_如何学运维b, i want to delete those entries so from each type & name pair there will be only one row.

any thoughts on how to do this?

---

That depends on what you want to keep and what you want to remove. Since ID is a key, I'm guessing that there are no duplicate ID's but duplicate type/name pairs. So here's an idea on how to remove them:

delete from my_table t1
where exists (select 1 
                from my_table t2
               where t2.type = t1.type
                 and t2.name = t1.name
                 and t2.id < t1.id)

That will keep the "duplicate" with the lowest ID

                 and t2.id > t1.id

That would keep the "duplicate" with the highest ID

---

Obviously change this query to a select statement first to ensure the correct records are being selected for deletion:

delete from table as t1
using table as t2
where t1.type = t2.type and t1.name = t2.name and t1.id > t2.id

---

You need to select distinct into a new table, then remove the old table and rename the new table. But there are lots of ways to get this done:

What's the best way to dedupe a table?

---

i ended up using the solution from this post: http://www.justin-cook.com/wp/2006/12/12/remove-duplicate-entries-rows-a-mysql-database-table/

basically, i've create a new table and copied the data from the old table to the new table without the duplications by using group by then i dropped the old table and renamed the new one.

Thanks All.