Jul 24, 2012
kalpesh

Mysql delete duplicate records leaving one

Mysql delete duplicate records leaving one row. Consider that there are many duplicate records in the table and you want to remove it. Now, we can select it by applying GROUP BY but the question is how to delete all the duplicate records (of field some_id below) EXCEPT one that should be there. So, lets say I have following table with duplicate data as shown:
Tablename: test

1
2
3
4
5
6
7
unique_id | some_id
--------------------
1         | 1
2         | 1
3         | 2
4         | 2
5         | 1


Solution:

1
DELETE t1 FROM test t1, test t2 WHERE t1.unique_id > t2.unique_id AND t1.some_id = t2.some_id;

Final Result:

1
2
3
4
unique_id | some_id
--------------------
1         | 1
3         | 2

1 Comment

  • Hii sir,

    How to delete all categories from Magento using query.

    Thanks
    Raushan Tiwari

Leave a comment

 

Welcome to my Blog

Kalpesh MehtaHelping Magento developers in their day-to-day development problems since 2011. Most of the problems and solutions here are my own experiences while working on different projects. Enjoy the blog and don't forget to throw comments and likes/+1's/tweets on posts you like. Thanks for visiting!

Certifications