delete duplicate record from same table in mysql

Question

I have one table named tblm_customer.

It contain field named firstname and lastname. Now I want to delete all records from the table that contain the same firstname and lastname that are already in the table.

I used mysql database and customerid is the primary key in the table.

Answer 1

Following delete removes all duplicates, leaving you with the latest CustomerID

A note of warning though. I don't know your use case but it is perfectly possible to have two people with the exact same name (we even had the addres being the same at one time).

DELETE  c1
FROM    tblm_customer c1
        , tblm_customer c2
WHERE   c1.FirstName = c2.FirstName 
        AND c1.LastName = c2.LastName 
        AND c1.CustomerID < c2.CustomerID