self join in update query with transitive data

Question

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.

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)

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.

(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:

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.

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

it is returning the output mentioned above, not which i actually wanted. — user3789961, Dec 17 '15 at 15:53
You need a recursive CTE to do this because there may be an indefinite number of transitions, like `a=b and b=c and c=d` then `a=d`. — Giorgos Betsos, Dec 18 '15 at 06:55
yes, can you the write the query with recursive CTE.. @GiorgosBetsos — user3789961, Dec 18 '15 at 13:14

score 1 · Answer 1 · answered Dec 17 '15 at 16:34

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

answered Dec 17 '15 at 16:34

banjo

11
1

i think we can achieve this by using recursive CTE.. do you have any idea? – user3789961 Dec 18 '15 at 06:24
huge data present in above tables. I ran this query,but it dint get executed from 2 days. – user3789961 Dec 25 '15 at 08:43
above query solved my problem. can you help me out using recursive query. thank you. :-) – user3789961 Feb 02 '16 at 07:04

Polux2 · Answer 2 · 2016-01-06T19:15:36.410

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

self join in update query with transitive data

2 Answers2