update 多表(转)

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                      111TOWN                         ID
-------------------- ----------
3435                        111
ljjjjj                      222
SQL> select * from wwm5;            --更新的条件表TOWN                         ID
-------------------- ----------
lllldf                      111
test                       9984SQL> 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                        1118 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                      111TOWN                         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 table1* 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                      111TOWN                         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                      222TOWN                         ID
-------------------- ----------
lllldf                      111
lllldf                      111
ljjjjj                      22214 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种关连更新的例子了,希望能给开发人员解惑