1

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.

Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
chetan
  • 3,175
  • 20
  • 72
  • 113
  • Do two records with the same firstname/lastname have also the same customerid? – eumiro Sep 22 '10 at 09:19
  • no customerid is unique as it is primary key – chetan Sep 22 '10 at 09:20
  • possible duplicate of [How to delete duplicate rows from a MySQL table](http://stackoverflow.com/questions/5770228/how-to-delete-duplicate-rows-from-a-mysql-table) – Basilevs Sep 16 '14 at 15:48

1 Answers1

5

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
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146