oracle 中的一个update 问题

[saschenkang]

现有两张表分别是 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中要如何实现

[java__king]

up

[sleepzzzzz]

update aaa t1
set t1.b = (select t2.b from bbb t2 where t1.a=t2.a)
where exists (select 1 from bbb where a=t1.a)

[BlueskyWide]

SQL> select * from t1;

         A          B
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1

6 rows selected

SQL> select * from t2;

         A          B
---------- ----------
         1          0
         2          0
         3          0
         4          0

SQL> 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 updated

SQL> select * from t1;

         A          B
---------- ----------
         1          0
         2          0
         3          0
         4          0
         5          1
         6          1

6 rows selected
SQL> 

[Quote=引用 1 楼 sleepzzzzz 的回复:]
SQL codeupdate aaa t1
set t1.b = (select t2.b from bbb t2 where t1.a=t2.a)
where exists (select 1 from bbb where a=t1.a)
[/Quote]

[codearts]

update
(
select aaa.a a1, bbb.a a2
from aaa, bbb
where aaa.a = bbb.a
)
set a1 = a2;
--aaa必须有主键方能执行上述代码