CODEWITHSUNDEEP
mysqlsql
Ran
Ran

Reputation: 3533

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 db, 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?

Upvotes: 2

Views: 255

Answers (4)

Ran
Ran

Reputation: 3533

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.

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 221275

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

Upvotes: 1

Daniel Pereira
Daniel Pereira

Reputation: 1785

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?

Upvotes: 0

Derek
Derek

Reputation: 23318

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

Upvotes: 0

Related Questions