这个UPdate的语句该怎么写啊？
现有两张表   User，   New_Old_User_Mapping
User：
    name         department
    u1             IT
    u2             ADMIN
    u3             Fin
    u4             HR
   

New_Old_User_Mapping:
    New_User     Old_User
    u1                 u2
    u3                 u4

希望把User表中u1,u3的department   的值更新为u2,u4的department的值(根据New_Old_User_Mapping的对应关系），Update   语句该怎么写啊？

即更新后的结果应该是：
User：
    name         department
    u1             ADMIN
    u2             ADMIN
    u3             HR
    u4             HR
   

    谢谢！！

------解决方案--------------------
create table test_user
(
uname varchar2(10),
dep varchar2(10)
);
insert into test_user
select 'u1 ', 'IT ' from dual
union all
select 'u2 ', 'admin ' from dual
union all
select 'u3 ', 'fin ' from dual
union all
select 'u4 ', 'hr ' from dual;
commit;

create table new_old_user
(
n_user varchar2(10),
o_user varchar2(10)
);

insert into new_old_user
select 'u1 ', 'u2 ' from dual
union all
select 'u3 ', 'u4 ' from dual;
commit;

update test_user b set b.dep=(SELECT
dep from (select a.n_user,c.dep
FROM new_old_user a ,test_user c
where a.o_user=c.uname ) t where b.uname=t.n_user) where exists
(select 'a ' from new_old_user bb where b.uname=bb.n_user);
commit;


测试通过！
------解决方案--------------------
create table userinfo (username varchar(20),deparment varchar(20));

insert into userinfo values ('u1','IT');
insert into userinfo values ('u2','ADMIN');
insert into userinfo values ('u3','FIN');
insert into userinfo values ('u4','HR');
commit;

create table New_Old_User_Mapping (new_user varchar(20),old_user varchar(20))

insert into New_Old_User_Mapping values('u1','u2');
insert into New_Old_User_Mapping values ('u3','u4');
commit;

update userinfo u set u.deparment = 
( select s.deparment from userinfo s, New_Old_User_Mapping n where s.username = n.old_user
and u.username = n.new_user)
where exists (select * from New_Old_User_Mapping m where u.username = m.new_user)