有两个表t1 t2
t1
a b
1 1
2 1
3 1
4 1
5 1
6 1t2a b
1 0
2 0
3 0
4 0
现要对t1做update,要求t1.a=t2.a时,将t2.b的值赋给t1.a
注意是在oracle中,不是在sql server中 我在sql server中用left join 实现了,可是在oracle中不知道怎么写
谢谢了,大侠们~
t1
a b
1 1
2 1
3 1
4 1
5 1
6 1t2a b
1 0
2 0
3 0
4 0
现要对t1做update,要求t1.a=t2.a时,将t2.b的值赋给t1.a
注意是在oracle中,不是在sql server中 我在sql server中用left join 实现了,可是在oracle中不知道怎么写
谢谢了,大侠们~
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7已选择7行。OPER@TL>select * from test2; AAA BBB
---------- ----------
1 11
2 22
3 33
5 55
6 66
7 77已选择6行。OPER@TL>update test a
2 set bbb=(select bbb from test2 b
3 where b.aaa=a.aaa)
4 where exists(
5 select 1 from test2 b
6 where a.aaa=b.aaa);已更新6行。OPER@TL>select * from test; AAA BBB
---------- ----------
1 11
2 22
3 33
4 4
5 55
6 66
7 77已选择7行。OPER@TL>
要求t1.a=t2.a时,将t2.b的值赋给t1.b
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>
set b=(select b from t2 where a=t1.a)
where exists (select 1 from t2 where a=t1.a);