update t1 set b=(select d from t2 where t2.c=t1.a) where exists(select c from t2 where t2.c=t1.a)
或者 update (select b,d from t1,t2 where t1.a=t2.c) set b=d
update t1 set b=(select d from t2 where t2.c=t1.a) where exists(select c from t2 where t2.c=t1.a)
SQL> select * from t1; A B --------------------- ---------- 1 a 2 b 3 c 4 d 5 eSQL> select * from t2; C D --------------------- -------------------- 1 c 2 d 3 c SQL> update t1 set t1.B = nvl((select t2.D from t2 where t1.A=t2.C),t1.B);5 rows updatedSQL> select * from t1; A B --------------------- ---------- 1 c 2 d 3 c 4 d 5 e SQL> select * from t2; C D --------------------- -------------------- 1 c 2 d 3 c
支持 bluelamb(bluelamb)的方法
bluelamb(bluelamb) 的update (select b,d from t1,t2 where t1.a=t2.c) set b=d 这个办法不行SQL> select * from T1;A B ---------- ---------- 1 a 2 d 3 c 4 d 5 eSQL> select * from T2;C D ---------- ---------- 1 c 2 b 3 cSQL> update (select b,d from t1,t2 where t1.a = t2.c) set b=d;update (select b,d from t1,t2 where t1.a = t2.c) set b=dORA-01779: 无法修改与非键值保存表对应的列SQL>
try this : update t1 set b=(select d from t2 where t2.c=t1.a and rownum=1) ;
where exists(select c from t2 where t2.c=t1.a)
update (select b,d from t1,t2 where t1.a=t2.c) set b=d
where exists(select c from t2 where t2.c=t1.a)
--------------------- ----------
1 a
2 b
3 c
4 d
5 eSQL> select * from t2; C D
--------------------- --------------------
1 c
2 d
3 c
SQL> update t1 set t1.B = nvl((select t2.D from t2 where t1.A=t2.C),t1.B);5 rows updatedSQL> select * from t1; A B
--------------------- ----------
1 c
2 d
3 c
4 d
5 e
SQL> select * from t2; C D
--------------------- --------------------
1 c
2 d
3 c
这个办法不行SQL> select * from T1;A B
---------- ----------
1 a
2 d
3 c
4 d
5 eSQL> select * from T2;C D
---------- ----------
1 c
2 b
3 cSQL> update (select b,d from t1,t2 where t1.a = t2.c) set b=d;update (select b,d from t1,t2 where t1.a = t2.c) set b=dORA-01779: 无法修改与非键值保存表对应的列SQL>
update t1 set
b=(select d from t2 where t2.c=t1.a and rownum=1) ;