现有两张表分别是 aaa 和bbb如下两表分别有两字段a,b
如下:
表aaa
a b
1 1
2 1
3 1
4 1
5 1表bbb
a b
1 0
2 0
3 0
4 0
对表aaa做update 在sql server中 可以实现: update aaa set aaa.b=bbb.b from aaa right join bbb on aaa.a=bbb.a
update后
表aaa
a b
1 0
2 0
3 0
4 0
5 1
但是oracle中实现不了 ,会报错,请问在oracle中要如何实现
如下:
表aaa
a b
1 1
2 1
3 1
4 1
5 1表bbb
a b
1 0
2 0
3 0
4 0
对表aaa做update 在sql server中 可以实现: update aaa set aaa.b=bbb.b from aaa right join bbb on aaa.a=bbb.a
update后
表aaa
a b
1 0
2 0
3 0
4 0
5 1
但是oracle中实现不了 ,会报错,请问在oracle中要如何实现
set t1.b = (select t2.b from bbb t2 where t1.a=t2.a)
where exists (select 1 from bbb where a=t1.a)
SQL> select * from t1; A B
---------- ----------
1 1
2 1
3 1
4 1
5 1
6 16 rows selectedSQL> select * from t2; A B
---------- ----------
1 0
2 0
3 0
4 0SQL> update t1 set
2 b=(select b from t2 where t1.a=t2.a)
3 where exists (select 1 from t2 where t1.a=t2.a);4 rows updatedSQL> select * from t1; A B
---------- ----------
1 0
2 0
3 0
4 0
5 1
6 16 rows selected
SQL>
(
select aaa.a a1, bbb.a a2
from aaa, bbb
where aaa.a = bbb.a
)
set a1 = a2;
--aaa必须有主键方能执行上述代码