使用传递数据自我加入更新查询

I have below tables with employee and his colleagues data,i want to update bucketid in Employee table, if there is matching Colleagues with the other employee.

我在员工和他的同事数据下面有表格，我想在员工表中更新bucketid，如果有匹配的同事与其他员工。

here in this example,

在这个例子中，

Employee = 101 is matching with Employee = 103 Colleague(i.e. c1) so both should have same bucketid = 1(i.e. min of both bucketids)

Employee = 101与Employee = 103同事（即c1）匹配，因此两者应该具有相同的bucketid = 1（即两个bucketids的min）

and Employee = 102 is matching with Employee = 103 Colleague(i.e. c3) so both should have same bucketid, but here it should be get updated with 1 as employee=102 bucketid has just changed to 1. we have transitive law dependent data in this example.

和Employee = 102匹配Employee = 103同事（即c3）所以两者都应该有相同的bucketid，但是在这里它应该更新为1作为employee = 102 bucketid刚刚更改为1.我们在此有传递法依赖数据例。

(i.e. a=b and b=c then a=c)

Employee table:

员工表：

EmployeeID  EmployeeName    BucketID
101         williams        1
102         williams        2
103         williams        3
104         williams        4

Employee_Colleague table:

Employee_Colleague表：

EmployeeID  Colleague
101         c1
101         c2
102         c3
102         c4
103         c1
103         c3
104         c7

I tried using this update query,

我尝试使用此更新查询，

 update a2
 set BucketID = a1.BucketID
 from Employee a1
 inner join Emp_Colleagues c1 on a1.EmployeeID=c1.EmployeeID 
 inner join Employee a2 on a1.EmployeeName=a2.EmployeeName
 inner join Emp_Colleagues c2 on a2.EmployeeID=c2.EmployeeID
 where c1.Colleague=c2.Colleague and a1.BucketID <> a2.BucketID

it is returning below output.

它返回低于输出。

EmployeeID  EmployeeName    BucketID
101         williams        1
102         williams        1
103         williams        3
104         williams        4

But i want output as below in Employee table.

但我想在Employee表中输出如下。

EmployeeID  EmployeeName    BucketID
101         williams        1
102         williams        1
103         williams        1
104         williams        4

2 个解决方案

#1

I don't think you can do this in one update statement. You could however use a loop to keep updating until there nothing left to update:

我不认为你可以在一个更新声明中这样做。但是，您可以使用循环继续更新，直到没有任何内容可以更新：

declare @updates int = 1
while @updates > 0
begin
    update a2
        set BucketID = a1.BucketID
        from Employee a1
        inner join Emp_Colleagues c1 on a1.EmployeeID=c1.EmployeeID
        inner join Emp_Colleagues c2 on c1.Colleague=c2.Colleague
        inner join Employee a2 on a2.EmployeeID=c2.EmployeeID
        where a1.BucketID < a2.BucketID
    set @updates = @@ROWCOUNT
end

#2

Here is the query you are looking for.

这是您要查找的查询。

with CTE as
(
select EmployeeID as E1, EmployeeID as E2, cast('\' as varchar(MAX)) as list
from Employee
Union all
select E1, T2_2.EmployeeID, CTE.list +  CAST(E1 as varchar(MAX)) + '-' + CAST(T2_2.EmployeeID as varchar(MAX)) + '\'
from CTE
    inner join Employee_Colleague T2_1 ON CTE.E2 = T2_1.EmployeeID
    inner join Employee_Colleague T2_2 ON T2_1.Colleague = T2_2.Colleague
where CTE.list not like '%\' + CAST(E1 as varchar(MAX)) + '-' + CAST(T2_2.EmployeeID as varchar(MAX)) + '\' +'%'
)

Update T1_1
Set T1_1.BucketID = (select MIN(T1_2.BucketID) 
                        from Employee T1_2 
                            inner join CTE ON T1_1.EmployeeID = CTE.E1 AND T1_2.EmployeeID = CTE.E2
                    )
from Employee T1_1

#1