Self join in update query with transitive data

Question

I have tables with the data of the employee and his colleagues, I want to update the bucketid in the Employee table, if there are corresponding colleagues with another employee.

here in this example

Employee = 101 is the same as Employee = 103 Colleague (i.e. c1), so both must have the same bucketid = 1 (i.e. min of both slaves)

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

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

Employees table:

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

Employee_Colleague table:

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

I tried using this update request,

 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

he returns below the exit.

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

, Employee.

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

Answer 1

, . , :

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

Answer 2

, .

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