SQL Server : update rows based other rows in the same table -

i using sql server in project, , have update column values of rows based on other rows in same table. here table:

| name | code | locale -------------------- |    | ab   | en |    | cd   | ar |    | ef   | ru | b    | gh   | ar 

i need update code values of rows locale not "en" based code value of row locale "en" , has same value in name. guaranteed each row "en" in locale has unique value in name. here hope achieve

| name | code | locale -------------------- |    | ab   | en |    | ab   | ar |    | ab   | ru | b    | gh   | ar 

i found thread @ update row data row in same table, , tried following methods none of them worked.

update mytable dt1, mytable dt2  set dt1.code = dt2.code  dt1.name = dt2.name    , dt1.code <> 'en'    , dt2.code = 'en'  update mytable t1  inner join mytable t2 on t1.name = t2.name , t2.code = 'en'  set t1.code = t2.code; t1.code <> 'en' 

in sql server, can join in update. correct syntax is:

update t    set code = teng.code    mytable t join         (select t2.*          mytable t2          t2.locale = 'en'         ) teng         on teng.name = t.name; 

actually, subquery isn't necessary:

update t    set code = teng.code    mytable t join         mytable teng         on teng.name = t.name , teng.locale = 'en'