跨表更新:
A表:id,name
B表:id,number,name实现目标:按照两表name相同的字段,把A表的id字段值更新到B表的number字段。
我的SQL语句:
update B set number=A.id from A where rtrim(A.name)=rtrim(B.name)
在TOAD下运行错误,提示:ORA-00933: SQL command not properly ended
谢谢!!!
A表:id,name
B表:id,number,name实现目标:按照两表name相同的字段,把A表的id字段值更新到B表的number字段。
我的SQL语句:
update B set number=A.id from A where rtrim(A.name)=rtrim(B.name)
在TOAD下运行错误,提示:ORA-00933: SQL command not properly ended
谢谢!!!
update b set num=(select id from a where a.name=b.name)
where exists (select 1 from a where a.name=b.name);
merge into b
using a on (a.name=b.name)
when matched then
update set b.num=a.id--另-方法
update (select /*+bypass_ujvc*/ b.num bnum,a.id aid from a,b where a.name=b.name)
set bnum=aid;
where exists (select 1 from a where a.name=b.name);