sql server - complex sql query to update main table -

i have 3 tables 1)main_table 2)flag_table 3)emp_flagdetails

main_table structure

 emp_no   hod_no  emp_name              flgtype e51397  e44417  asha v   e42342  e44417  shaikh faiz ahmed    e06636  e44417  balu  k u    

in above table kept flgtype column blank update later have flag_table structure follow

 flagid  flagcategory    flagtype 1         bm            brml12b 2         bm            brmm12b 3         bm            brmrmb 4         bm            brmcmb 5         bm            brmzm 6         vh            brml12v 7         vh            brmm12v 8         vh            brmrmv 9         vh            brmcmv 

emp_flagdetails structure follow

 ecode   flag e44417  brml12b e42342  brmrmb e06636  brmzm e51397   brml12b 

tables structure,now query want update flgtype coloumn of main_table flagcategory column of flag_table ..in such way if empno main_table present in emp_flagdetails table check flag column of emp_flagdetails matching employee , flag column value , find value in flag_table if present in flag_table ,we update main_table flgtype column flagcategory column value....so output follow

 emp_no   hod_no  emp_name              flgtype e51397  e44417  asha v                 bm e42342  e44417  shaikh faiz ahmed      bm e06636  e44417  balu  k u              bm 

please me write query

query:

sqlfiddleexample

update m set m.flgtype = f.flagcategory main_table m  join emp_flagdetails fd   on fd.ecode = m.emp_no     join flag_table f   on f.flagtype = fd.flag;