table a table b table c
id name sex name alias id name sex
1 张三 m 张三 老三
2 大头 m 王二 大头
3 小头 f 李四 小头
表a中的大头是个外号,表b是人名和外号的对照表,现在我想把表a中的所有外号改成真名,再写到c中去,a表不动
现在是先把表a的内容复制到c
insert into c select id,name from a;
然后再更新
update c
set (c.name) =(select b.name from b inner join a on a.name=b.alias where c.name=b.alias)
where c.name in (select b.alias from b inner join a on a.name=b.alias ) ;
-------------------------------------以上是a中的name没有重复的情况(如有重复
-------------------------------------就把第二行改为set (c.name) =(select b.name from b inner join a on a.name=b.alias where c.name=b.alias and ROWNUM=1)
网上那个更新语句
update c
set (c.name) =(select b.name from b inner join a on a.name=b.alias)
where exists (select 1 from b inner join a on a.name=b.alias);
总是提示
set (c.name) =(select b.name from b inner join a on a.name=b.alias where a.name=b.alias)
*
第 2 行出现错误:
ORA-01427: 单行子查询返回多个行
如果把第二行改为 set (c.name) =(select b.name from b inner join a on a.name=b.alias where ROWNUM=1)就会把c表全改成一个名字
------------------------------------------------------------------
仅供大家参考,希望对大家有所帮助