实时测试的速度:
--48466条数据
--1.297
inline view更新法
inline view更新法就是更新一个临时建立的视图
update (select a.join_stateas join_state_a,b.join_stateas join_state_b
from t_join_situation a, t_people_info b where a.people_number=b.people_number
and a.year='2011' and a.city_number='M00000' and a.town_number='M51000'
) set join_state_a=join_state_b
括号里通过关联两表建立一个视图,set中设置好更新的字段。这个解决方法比写法较直观且执行速度快。但表B的主键一定要在where条件中,并且是以“=”来关联被更新表,否则报错误
--7.156
update t_join_situation aset a.join_state=(select b.join_statefrom t_people_info b
where a.people_number=b.people_number
and a.year='2011' and a.city_number='M00000' and a.town_number='M51000')
where exists (select 1 from t_people_info b
where a.people_number=b.people_number
and a.year='2011' and a.city_number='M00000' and a.town_number='M51000')
--3.281
begin
for crin (select a.rowid,b.join_statefrom t_join_situation a,t_people_info b
where a.people_number=b.people_number
and a.year='2011' and a.city_number='M00000' and a.town_number='M51000')loop
update t_join_situationset join_state=cr.join_statewhere
rowid = cr.rowid;
end loop;
end;
--1.641
merge into t_join_situation a
using t_people_info b
on (a.people_number=b.people_number
and a.year='2011' and a.city_number='M00000' and a.town_number='M51000')
when matched then update set a.join_state=b.join_state