如下两种更新语句哪个效率更高?
方式一:
UPDATE TA a
SET m1 = (SELECT k1 FROM TB b WHERE a.id = b.id),
m2 = (SELECT k2 FROM TB b WHERE a.id = b.id),
m3 = (SELECT k3 FROM TB b WHERE a.id = b.id),
m4 = (SELECT k4 FROM TB b WHERE a.id = b.id);
方式二:
for cur_value in (select id,k1,k2,k3 from TB )
loop
updte TA
set m1 = cur_value.k1,
m1 = cur_value.k1,
m1 = cur_value.k1,
m1 = cur_value.k1
where TA.id = cur_value.id;
end loop;
[解决办法]
应该加上条件，否则TB中不存在的记录m1, m2, m3, m4都会变为null
UPDATE TA a
SET (m1, m2, m3, m4) = (select k1, k2, k3, k4 from TB where id=a.id)
WHERE EXISTS (SELECT * from TB where id = a.id)
