Remove duplicate records using mysql query

As a developer we always require to remove duplicate field values from our db tables. Some time we are in hurry and we don’t want to do brain storming to find out the solution of this question. So i have decided to write these special sql query so that we can get this solution easily.

In my example i have taken a dummy table named ’email’ which have only two field. 1. Id (auto incremented) 2. Email (customers email)

Mysql query to find duplicate record count into the database

SELECT *, count(*) as n FROM email group by email HAVING n>1

id             email                                                                     n
15         balwanmehra <[email protected]>,                 2
16         dkkhosla <[email protected]>,                     2

Mysql query to remove duplicate field value from a database table

To achieve this we will copy all unique records in a new tables by below mysql query

INSERT INTO email_new (email)
SELECT email FROM email group by email HAVING count(*)>0

now delete old table using below query

drop table email

rename new table with old table name by using below query

rename table email_new to email;

The following two tabs change content below.

• Bio
• Latest Posts

Chandra Shekhar

GCP Architect

Chandra Shekhar Pandey is Google certified Cloud engineer, I am Magento2 Trained developer. Having huge experience in designing cloud solution. I have around 12 years of experience with world enterprise IT companies and fortune 500 clients. During my architecture design I am always caring about high availability, fast performance and resilient system. From the programmer background I have huge experience in LAMP stack as well. Throughout my carrier I have worked on Retail, E-Learning, Video Conferencing and social media domain. The motive of creating cutehits was just to share the knowledge/solutions I get to know during my day to day life so that if possible I can help someone for same problems/solutions. CuteHits.com is a really a very effort for sharing knowledge to rest of the world. For any query/suggestion about same you can contact me on below details:- Email: shekharmca2005 at gmail.com Phone: +91-9560201363

Latest posts by Chandra Shekhar (see all)

• Best practices for micro service design - January 23, 2022
• Spring Boot - January 23, 2022
• Java - January 23, 2022