Update关联select更新

有时候，我们需要用b表中的字段值更新a表中的字段值，同时会附加a表与b表的条件限制。

1、错误的做法

update tableA a set a.field1 = (select b.field1 from tableB where a.field2 = b.field2)

这种做法在更新a表符合条件值的同时也会把不符合条件的值更新掉

2、正确做法

方法一:
set town=(select town from wwm5 where wwm5.id=wwm2.id) where id=(select wwm5.id from wwm5 where wwm5.id=wwm2.id)  

update TBL_OA_PHONEUSER a set  DEPTNAME = (select deptment from TEMP_USERINFOR b where a.TELEPHONE=b.phone) where a.TELEPHONE=(select phone from TEMP_USERINFOR b where a.TELEPHONE=b.phone)

方法二:    与方法一道理相同,这里需要掌握EXIST的相关用法.
update TBL_OA_PHONEUSER a set DEPTNAME =(select deptment from TEMP_USERINFOR b where a.TELEPHONE=b.phone) where exists (select 1 from TEMP_USERINFOR b where a.TELEPHONE=b.phone)

方法三:
update (select a.DEPTNAME as dept,b.deptment as deptment from TBL_OA_PHONEUSER a,TEMP_USERINFOR b where a.TELEPHONE=b.phone) set dept=deptment  

这种方法的局限性就是需要PRIMARY 的支持.

方法四:
 declare
 cursor cur_userphone is select deptment,phone from TEMP_USERINFOR;
 begin
   for my_userphone in cur_userphone loop
   update TBL_OA_PHONEUSER set DEPTNAME=my_userphone.deptment
   where TELEPHONE=my_userphone.phone;
   end loop;
 end;