更新具有重复找到的ID的重复行 [英] Update duplicate rows with duplicated found id
本文介绍了更新具有重复找到的ID的重复行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的表:
id name description
1 foo
2 bar
3 foo
我想要更新ID与重复行的描述。如下所示:
i want update description with id of duplicated row . something like:
id name description
1 foo duplicate in id (3)
2 bar
3 foo duplicate in id (1)
我如何在Mysql中执行此操作
How can i do this in Mysql
推荐答案
此查询将返回所有重复的ids,并以逗号分隔的ids名称共享相同的名称:
This query will return all duplicated ids with a comma separated list of ids that share the same name:
select
t1.id,
group_concat(t2.id)
from
tablename t1 inner join tablename t2
on t1.id<>t2.id and t1.name=t2.name
group by
t1.id
,此查询将更新说明:
update tablename inner join (
select
t1.id,
group_concat(t2.id) dup
from
tablename t1 inner join tablename t2
on t1.id<>t2.id and t1.name=t2.name
group by
t1.id
) s on tablename.id = s.id
set
description = concat('duplicate id in (', s.dup, ')')
请看一个< a href =http://sqlfiddle.com/#!9/557f2/1 =nofollow>工作小提琴在这里。
这篇关于更新具有重复找到的ID的重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文