--try

create table table1(col1 varchar(10),col2 varchar(10))
go
insert into table1 select '001','002'
create table table2 (empno varchar(10),empname varchar(10))
go
insert into table2 select '001','aaa'union all select '002','bbb'

update T set col1 = (select empname from table2 where empno = T.col1 ) ,
             col2 = (select empname from table2 where empno = T.col2 )  from table1 T

select * from table1

drop table table1,table2

select a.empname,b.empname 
from table1 c,table2 a,table2 b
where c.字段一=a.empno and c.字段二 =b.empno 

select a.empname,b.empname 
from 
(select c.字段二,d.empname from table1 c,table2 d where c.字段一 = d.empno) a,table2 b
where  a.字段二 =b.empno 

create table table1(col1 varchar(10),col2 varchar(10))
go
insert into table1 select '001','002'

create table table2 (empno varchar(10),empname varchar(10))
go

insert into table2 select '001','aaa'union all select '002','bbb'

update table1
set
col1 = (select empname from table2 where empno = table1.col1 ),
col2 = (select empname from table2 where empno = table1.col2 )

select