sql - Update based on hierarchy table -

i have table of employees- active or inactive . structure below

create table employee ( id int, supervisorid int, active bit ) 

another table component has items ..

create table component  ( cid int, empid int ) 

have sample data loaded here

how can update component table , such empid field holds immediate active supervisor id inactive employees. in sample case, result of update should

component --------- cid  | empid --------------  1     1235   2     1246  3     1246 

i able generate hierarchy table specific employees using query mentioned in fiddle page.

you can use recursive cte make table update first active supervisorid each non active id. on first iteration select active id's , set baseid same. on each next iteration select next level of non-active childs add recursive table , save root baseid in each record. when add records baseid field each id first active parent. leave id's need update (where id<>baseid) in forupd table. use table id->baseid update original table.

;with orgchart         (           select id,id baseid   employee active=1   union    select e.id, g.baseid baseid            employee e    join orgchart g on e.supervisorid=g.id   e.active=0 ), forupd (select id,baseid orgchart id<>baseid)   update set empid = b.baseid  component  join forupd b on a.empid = b.id 

sqlfiddle demo