从MySQL中的同一张表中删除重复的记录 [英] delete duplicate record from same table in mysql

问题描述

我有一个名为tblm_customer的表.

它包含名为firstname和lastname的字段.现在，我想从表中删除所有包含表中已经包含的名字和姓氏的记录.

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.

我使用了mysql数据库，customerid是表中的主键.

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

推荐答案

以下删除操作将删除所有重复项，并为您提供最新的CustomerID

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 

这篇关于从MySQL中的同一张表中删除重复的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！