Find duplicate entry in mysql database and delete


SELECT MAX(ID), Name, EmailId, MobileNo FROM `tbl_leaddata` GROUP BY Name, EmailId, MobileNo having count(*) > 1 order by ID ASC

OR

SELECT n1.ID FROM tbl_leaddata n1, tbl_leaddata n2 WHERE n1.ID > n2.ID AND n1.Name = n2.Name AND n1.EmailId = n2.EmailId AND n1.MobileNo = n2.MobileNo

Note : if 2 entry found in mysql database with 2 id. first id is 4 and second id is 32 and both are same entry then above query show id 32 which are duplicate entry/id.

if you want to delete duplicate entry use below query :

DELETE n1 FROM tbl_leaddata n1, tbl_leaddata n2 WHERE n1.ID > n2.ID AND n1.Name = n2.Name AND n1.EmailId = n2.EmailId AND n1.MobileNo = n2.MobileNo

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s