将一行的列与另一行的相同列进行比较，然后如果匹配则更新第三列 [英] compare column of one row with same column of other row and then if there is match update the third column

问题描述

我的桌子上有100万条记录。

我想将第一行中名为name的一个字段与另一行的相同字段进行比较，如果匹配，则更新具有该名称的列parent。我该怎么办...？



例如：



名称区域父母

------------------------------------

诺基亚610立方体

投资240 ahnc

投资241 hnnc

apple 500 sumo

Iphone 210 robert



所以当Name = Name时，然后用这个名字更新Parent



在这种情况下：



名称区域父母

------------------------------ ------

诺基亚610立方体

投资240 ahnc投资

投资241 hnnc投资

apple 500 sumo

Iphone 210 robert

Hi, I have 1 Million records in my table.
I want to compare one field called "name" for 1st row to same field of other row and in case of match update a column "parent" with that name . How do i do..?

Example :

Name code area Parent
------------------------------------
Nokia 610 cube
Investments 240 ahnc
Investments 241 hnnc
apple 500 sumo
Iphone 210 robert

so when Name = Name , then update Parent with that name

in this case :

Name code area Parent
------------------------------------
Nokia 610 cube
Investments 240 ahnc Investments
Investments 241 hnnc Investments
apple 500 sumo
Iphone 210 robert

推荐答案

将查询分解为概念：



您只需要查看至少（或仅？）两次存在的值。所以你需要分组并计算它们。





然后，所有那些有计数> 1（你可能需要使用一个HAVING子句）可以在子查询中返回UPDATE语句设置parent = name



现在，改进这个：节省处理时间，您可能会发现通过检查以确保每个集合中至少有一个尚未被处理来减少UPDATE列表是有用的。这可以在UPDATE中使用WHERE子句来处理。

Break the query up into concepts:

You only need to look at values that exist at least (or only?) twice. So you need to group-by and count them.


Then, all those with a count > 1 (you may need to use a HAVING clause) can be returned in a subquery for an UPDATE Statement setting parent=name

Now, refine this: to save processing time, you may find it useful to reduce the list for the UPDATE by checking to make sure at least one in each set has not already been attended to. This could be handled with a WHERE clause in your UPDATE.

以下是必需的查询为了你。

following is the required query for you.

update TableName set parent = name where  name in (	SELECT NAME FROM TableName GROUP BY Name having count(NAME) > 1)

希望这会对你有帮助。

Hope this will helps you.

希望这个查询能得到结果。由于涉及更新，请在使用真实记录进行测试之前使用样本开发数据进行尝试。如果你使用2012，请尝试使用Lead Functions来摆脱这个CTE。

Hi,

Hope this query will get the result. Since update is involved please try this with sample development data before testing with real records. If you are using 2012 please try with Lead Functions which will get rid of this CTE.

create table #tempDha(Id varchar(50),Code int,Parent varchar(50))

insert into #tempDha values('Nokia',610, 'cube')
,('Invest' ,240 ,'ahnc')
,('Invest', 241 ,'hnnc')
,('apple' ,500 ,'sumo')
,('Iphone', 210 ,'robert' )

，cte为

（

选择dense_rank（）over（PARTITION by Id order by code）as Ra，* from #tempDha）

update t set t.Parent = t.Parent +''+ t.Id

from #tempDha t join cte c on t.Id = c.Id

其中Ra> 1



谢谢

Dharani

with cte as
(
select dense_rank()over(PARTITION by Id order by code) as Ra,* from #tempDha)
update t set t.Parent= t.Parent + ' '+ t.Id
from #tempDha t join cte c on t.Id=c.Id
where Ra>1

Thanks
Dharani

这篇关于将一行的列与另一行的相同列进行比较，然后如果匹配则更新第三列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！