现有两张表 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
谢谢!!
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
谢谢!!
from user a inner Join New_Old_User_Mapping b
on a.name=b.Old_User and user.Name=New_User)
Select s.department from User s,New_Old_User_Mapping t
Where s.names=t.Old_User and t.New_User=d.NAMES),department)
测试通过,给分吧,谢谢!
update user a set a.department=(select b.department from user b where b.name=u2) and a.name=u1;
update user a set a.department=(select b.department from user b where b.name=u4) and a.name=u3;
set u.department = (
select uu.department
from User uu
where exists (select 1
from New_Old_User_Mapping n
where u.name = n.New_User
and uu.name = n.Old_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;
测试通过!
ls那么多sql,都不行么
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)