合并,然后删除重复的条目 [英] 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屋!