合并，然后删除重复的条目 [英] Merge and then Delete duplicate entries

问题描述

我有一些重复条目的mySQL数据库.他们有相同的领域-电话.但是他们也有不同的领域.例如，我有两个条目使用同一部手机，但是第一个条目的等级为= default_value，第二个条目的等级字段为5. 因此，我必须合并这些条目，然后才删除重复项...

I have mySQL database with some duplicate entries. They have the same field - phone. But they also had fields which differs. At example I have two entries with same phone, but first entry has rating filed = default_value and second entry has rating field = 5. So I must merge this entries and only then delete duplicates...

更常见的示例:

    entry1.phone==123
entry1.phone==etry2.phone
    entry1.rating!=entry2.phone
    entry1.rating==default_value(0)
    entry2.rating==5
    merge
    entry1.phone==123
    entry1.rating==5
    entry2 is deleted

推荐答案

听起来，如果您只是尝试使用非默认等级更新第一条记录，则您实际上并不需要合并任何记录.我认为您可以删除具有默认评分的任何记录.

It sounds like you don't really need to merge any records if you are just trying to update the first record with the non-default rating. I think you can just delete any records with the default rating.

Select a.*
from tbl a
inner join tbl b
on a.Phone = b.Phone
    and a.Rating < b.Rating


Delete a
from tbl a
inner join tbl b
on a.Phone = b.Phone
    and a.Rating < b.Rating

如果确实需要更新第一条记录并删除第二条记录，那么如果您具有自动增量ID，则可以执行类似的操作.下一个示例是在存在ID的情况下如何更新第一条记录的操作.仅当您重复拨打一次电话号码时，这才是可靠的.

If you truly have to update the first record and delete the second record, you can do something similar if you have an autoincrement ID. The next example is what I would do to update the first record if an ID exists. This is only reliable if you only have phone numbers duplicated one time.

Update a
Set a.Rating = b.Rating
from tbl a
inner join tbl b
on a.Phone = b.Phone
    and a.Rating < b.Rating
    and a.ID < b.ID


Delete a
from tbl a
inner join tbl b
on a.Phone = b.Phone
    and a.Rating = b.Rating
    and b.ID > a.ID

希望这会有所帮助.

-Ranthalion

-Ranthalion

这篇关于合并，然后删除重复的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！