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’)

 

Select custom date format from table where store created field in UnixTimeStamp data

//created field int(11) type in table : NewTB
$sql = mysql_query(“select  *, FROM_UNIXTIME(created, ‘%d-%m-%Y %h:%i:%s’) from NewTB where FROM_UNIXTIME(created, ‘%Y’) = 2016”);

output : 30-06-2016 10:45:35

$sql = mysql_query(“select  *, FROM_UNIXTIME(created, ‘%d-%m-%Y’) from NewTB where
FROM_UNIXTIME(created, ‘%Y’) = 2016”);
output : 30-06-2016