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

Update bulk row in mysql database in single query

UPDATE tbl_update SET Code= (
case
when ID = ‘7’ then ‘A2M2’
when ID = ‘8’ then ‘A2M3’
when ID = ‘9’ then ‘A2M4′
when ID = ’10’ then ‘A2M5′
when ID = ’11’ then ‘A2M9’
end
) WHERE ID IN (‘7′,’8′,’9′,’10’,’11’)