Remove the duplicate primary keys from a Mysql database using php programming

advertisements

I have three fields in my database: first name, lastname and email. Email is my primary key. I don't have any other fields in my database.

I need to find a mysql query which can delete duplicated primary keys and their values from the database leaving only one unique email in the database.

I use the following command to display all duplicated primary keys. It worked, but I need to delete all other duplicate entries and keep only one. I am using php programming.

SELECT *
FROM table_name
WHERE primarykey IN (
SELECT primarykey
FROM table_name
GROUP BY primarykey
HAVING count(primarykey) > 1
)
ORDER BY primarykey


Populate a temp table with the ones you want to keep, using GROUP BY, HAVING and MAX on the other columns. Then run your query which deletes too much, then put your copied ones back in. And then make it the actual PK so it doesn't happen again.