有时候，我们需要用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)
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 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
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;
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;