Query to find and remove duplicate data from MYSql table

This is the very often problem to handle duplicate data in mysql table, If you are working on huge amount of data, You might have face this problem, So in this post I am going to show you MYsql query by which you can find duplicate data and remove it. This is also the most ask-able question during the time of interview.

Suppose we have a 25k companies in our database, And there is some duplicate companies exist in your companies table and you want find duplicate companies and want to completely remove from your companies table then use below mysql queries.

TB_COMPANIES

In Above table there is duplicate records of company HCL we have to find and remove it.

MYSql query to find duplicate data.

SELECT COMPANY_NAME
FROM TB_COMPANIES
WHERE COMPANY_NAME
IN (
SELECT COMPANY_NAME
FROM TB_COMPANIES
GROUP BY COMPANY_NAME
HAVING COUNT( COMPANY_NAME ) >1
)

MYSql query to delete duplicate data.

DELETE C1 
FROM TB_COMPANIES C1,TB_COMPANIES C2 
WHERE C1.ID < C2.ID 
AND C1.COMPANY_NAME = C2.COMPANY_NAME

Above you saw, Using MYsql sub query and self-join feature you can easily write query to find and delete duplicate data.

If you like this post please don’t forget to subscribe my public notebook for more useful stuff

Related