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.
|1||HCL LTD.||NOIDA||XXXXXXX||[email protected]||xxxxxxxx|
|3||HCL LTD.||NOIDA||XXXXXXX||[email protected]||xxxxxxxx|
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.