mysql多表关联更新和in效率问题

时间:2022-08-05 13:29:41
#重新下发投放配置,只下放投放状态的单子

1.单表where查询更新

UPDATE bus_execute_sync b SET b.flag = 0 WHERE b.execid IN(SELECT id FROM bus_execute a WHERE a.status IN('10','20','21','22','23','24','25'));

2.表关联更新

UPDATE bus_execute_sync b,bus_execute c SET b.flag = 0 WHERE c.status IN ('10','20','21','22','23','24','25') AND b.execid = c.id;


3.表关联之前先过滤部分数据,该方法奇慢无比,不要使用
UPDATE bus_execute_sync b,
(SELECT a.id FROM bus_execute a WHERE a.status IN ('10','20','21','22','23','24','25')) c SET b.flag = 0 WHERE b.execid = c.id;