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.

Do two records with the same firstname/lastname have also the same customerid? — eumiro
– eumiro, Commented Sep 22, 2010 at 9:19
possible duplicate of How to delete duplicate rows from a MySQL table — Basilevs
– Basilevs, Commented Sep 16, 2014 at 15:48

Lieven Keersmaekers · Accepted Answer · 2010-09-22 13:04:01Z

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

edited Sep 22, 2010 at 13:04

answered Sep 22, 2010 at 9:24

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chetan Over a year ago

it will generaERROR 1093 (HY000): You can't specify target table 'TBLM_CUSTOMER' for update in FROM clausete error like

Lieven Keersmaekers Over a year ago

@chetan, I've altered the statement. Previous statement worked with SQL Server. This should work using MySQL.

Collectives™ on Stack Overflow

delete duplicate record from same table in mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related