这个UPdate的语句该怎么写啊？

[cadwolf]

现有两张表 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


谢谢！！

[WangZWang]

update user set department=(Select a.department
from user a inner Join New_Old_User_Mapping b
on a.name=b.Old_User and user.Name=New_User)

[cadwolf]

完全错误～～

[WangZWang]

update User d set department= nvl((
Select s.department from User s,New_Old_User_Mapping t
Where s.names=t.Old_User and t.New_User=d.NAMES),department)

[cadwolf]

这样肯定不行啊，你这是把user所有的记录都更新了。。没有更新的条件啊。。

[cadwolf]

拜托，请先测试一下再发上来，谢谢～～

[shine_spiritmoon]

update user a set dep=(select dep from user b,map c where b.name=c.old and a.name=c.new) where name in (select new from map)
测试通过，给分吧，谢谢！

[WangZWang]

你自己都没有试就说不行，上面语句能实现你的要求。
当然也可以加一个条件

[boyofcity]

你这个要求不能用一个语句实现，只能分别来：
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;

[mantisXF]

update User u
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
)
);

[fufengchun]

update user a set dep=(select dep from user b,map c where b.name=c.old and a.name=c.new) where name in (select new from map)


这条语句可以实现你的功能，而且最简洁

[cadwolf]

To WangZWang(先来): 我当然试过的，不行。。

[kinglht]

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;


测试通过！

[liuzi123]

ls放在SQL中不行

[beibeishen]

临时表都出来了,汗...
ls那么多sql,都不行么

[leblang]

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)