从"MySQL"中删除重复项并保持单个记录 [英] Delete duplicates from `MySQL` and keep single record

问题描述

我正在使用以下查询来查找重复的记录，并已验证它是否可以正常运行.

I am using the following query to find duplicate records and have verified it runs correctly.

SELECT MLS_LISTING_ID, STREET_NUMBER, STREET_NAME, UNIT_NUMBER, MLS_ID, SALE_PRICE, ZIP_CODE, TLN_REALTOR_ID, COUNT(mls_id)
FROM idx_FM_BO_NA
GROUP BY TLN_REALTOR_ID, STREET_NUMBER, STREET_NAME, UNIT_NUMBER, SALE_PRICE
HAVING COUNT(distinct MLS_ID) > 1; 

如何更改此查询以删除重复项，以使记录只有一个实例?我不担心删除哪条记录，但是我需要其中一条保留.

How do I alter this query to delete duplicates so that there is only one instance of the record? I don't worried which record(s) get deleted, but I need one of them to stay.

推荐答案

我认为这个答案是正确的:

I think this answer is correct:

delete from table1 USING table1, table1 as vtable 
WHERE table1.ID<vtable.ID AND table1.field_name=vtable.field_name

这个和paulsm4的答案之间的唯一区别是，使用小于等于而非不等于来比较id.这样，以后的记录就不会与以前的记录进行比较(这意味着将保留一个且只有一个相同的记录).我用自己需要的数据对它进行了测试，并且可以正常工作.

The only difference between this one and paulsm4's answer that the ids are compared using less-than rather than not-equals. That way, later are not compared with earlier records (which means that one and only one of the identical records will be kept). I tested this out with my own data that I needed this for, and it worked.

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