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 <abc@rediffmail.com>,                 2
16         dkkhosla <hosla@yahoo.com>,                     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;

Related posts:

The following two tabs change content below.

Chandra Shekhar

Consultant at Infogain
Chandra Shekhar is a LAMP Developer,Thinker,Blogger and currently working as Magento(Ecommerce) specialist.. I am always open to learn new things in LAMP as well as other newer technology. In the journey of my last 9 years of web and product development, I have decided to share my knowledge and experience to Open Source Community in 2011. In this regard I have started CuteHits.com. Since then I am actively sharing my knowledge on various LAMP technology through CuteHits.com’s technical ... Read More about Chandra Shekhar

Latest posts by Chandra Shekhar (see all)