avoid subquery in mysql update statement

公司最近有個deployment，要run個SQL patch。係QA 同staging 都冇事，點知一去production run左30mins 都未行完，terminated & rollback.

原來係其中一個update statement 行subquery，where id = (select  …) mysql 會run select on each row 🙁  DataSet 一大，慢到嘔。

CREATE TABLE alarm_backup SELECT * FROM alarm;  

UPDATE alarm a
SET a.status = 'active' , ....... 
WHERE a.id = (
  SELECT al.id 
  FROM alarm_backup ab 
    JOIN device d ON ab.device_id = d.id
    JOIN ......
  WHERE .....
);

其實subquery 係唔需要⋯⋯

CREATE TABLE alarm_backup SELECT * FROM alarm;  

UPDATE alarm a
JOIN device d ON a.device_id = d.id
JOIN ......
SET a.status = 'active' , ....... 
WHERE ..... ；

故事教訓我地：
1) 放假返黎之後小心點，你唔係到個時既Peer review 都要睇返。
2) 唔好咁容易信你上司，上左神枱技術係會回塘 😂