本文转自：http://blog.csdn.net/disiwei1012/article/details/52589181
http://www.blogjava.net/Jhonney/archive/2010/06/25/324503.html
$ sqlplus user/pass SQL*Plus: Release 9.2.0.6.0 - Production on Wed Aug 2 17:38:39 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production SQL> select * from wwm2; --要更新的表 TOWN ID -------------------- ---------- 222 222 111 111 ww'jj 111 llll 1111 dddd 2222 lllldf 111 lllldf 111 dsafdf 111 3435 111 ljjjjj 222 dsafdf 111 TOWN ID -------------------- ---------- 3435 111 ljjjjj 222 SQL> select * from wwm5; --更新的条件表 TOWN ID -------------------- ---------- lllldf 111 test 9984 SQL> select wwm2.* from wwm2,wwm5 where wwm2.id=wwm5.id 2 / TOWN ID -------------------- ---------- 111 111 ww'jj 111 lllldf 111 lllldf 111 dsafdf 111 3435 111 dsafdf 111 3435 111 8 rows selected. 所以,每次需要更新8条数据就是正确的. 相信程序员是通过以下类似的SQL更新的,这是错误的,因为没有加WHERE SQL> update wwm2 set wwm2.town=(select wwm5.town from wwm5 where wwm5.id=wwm2.id) 2 / 13 rows updated. SQL> select * from wwm2; TOWN ID -------------------- ---------- 222 lllldf 111 lllldf 111 1111 2222 lllldf 111 lllldf 111 lllldf 111 lllldf 111 222 lllldf 111 TOWN ID -------------------- ---------- lllldf 111 222 13 rows selected. 可以看到13条记录被更新,符合条件的更新正确,不符合条件的也更新为NULL.以下是正确的方法 方法一: SQL> update wwm2 2 set town=(select town from wwm5 where wwm5.id=wwm2.id) 3 where id=(select wwm5.id from wwm5 where wwm5.id=wwm2.id) 4 / 8 rows updated. 方法二: 与方法一道理相同,这里需要掌握EXIST的相关用法. SQL> update wwm2 set town=(select town from wwm5 where wwm5.id=wwm2.id) where exists (select 1 from wwm5 where wwm5.id=wwm2.id) 8 rows updated. 方法三: SQL> update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id) 2 set atown=btown 3 / set atown=btown * ERROR at line 2: ORA-01779: cannot modify a column which maps to a non key-preserved table 1* alter table wwm5 add primary key (id) SQL> / Table altered. 1 update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id) 2* set atown=btown SQL> / 8 rows updated. 这种方法的局限性就是需要PRIMARY 的支持. 方法四: 1 declare 2 cursor cur_wwm is select town,id from wwm5; 3 begin 4 for my_wwm in cur_wwm loop 5 update wwm2 set town=my_wwm.town 6 where id=my_wwm.id; 7 end loop; 8* end; SQL> / PL/SQL procedure successfully completed. SQL> select * from wwm2; TOWN ID -------------------- ---------- 222 222 lllldf 111 lllldf 111 llll 1111 dddd 2222 lllldf 111 lllldf 111 lllldf 111 lllldf 111 ljjjjj 222 lllldf 111 TOWN ID -------------------- ---------- lllldf 111 ljjjjj 222 这个方法是最灵活的了. 方法五: 注意,方法五只能适用于WWM5是WWM2的子集的时候. 1 merge into wwm2 2 using (select town,id from wwm5) b 3 on (wwm2.id=b.id) 4 when matched then update set town=b.town 5* when not matched then insert (town,id) values (null,null) SQL> / 9 rows merged. SQL> select * from wwm2; TOWN ID -------------------- ---------- ---注意这个地方,被插入了一个空值.因为WWM5的ID=9984在WWM2中不能匹配,根本原因是ORACLE9必须有WHEN NOT MATCHED子句,但是ORACLE10可以不许要,也就是ORACLE10可以不写WHEN NOT MATCHED ,就不必插入NULL值了,为解决这个问题,下一步会DELETE WWM5的ID=9984,这样一来就不会执行WHEN NOT MATCHED 222 222 lllldf 111 lllldf 111 llll 1111 dddd 2222 lllldf 111 lllldf 111 lllldf 111 lllldf 111 ljjjjj 222 TOWN ID -------------------- ---------- lllldf 111 lllldf 111 ljjjjj 222 14 rows selected. SQL> delete from wwm5 where id=9984; 1 row deleted. SQL> 1 merge into wwm2 SQL> 2 using (select town,id from wwm5) b SQL> 3 on (wwm2.id=b.id) SQL> 4 when matched then update set town=b.town SQL> 5* when not matched then insert (town,id) values (null,null) SQL> / 8 rows merged. 以上就是5种关连更新的例子了,希望能给开发人员解惑. 说明：如果select 子句可以返回多行记录，但返回适合where条件的记录只能是唯一的，否则将会报返回单行的select子句返回多行的错误，因为update只能跟据此处的where子句（内层where)进行相应记录的匹配更新，一次只能是一条。