[SQL] 對一張 table 的 multiple rows 做更新

公司的源碼裡，有一段程式碼，被公司的資深工程師挑出來說需要重構。本來的程式碼做的事情是： 「對一張 table 的 multiple rows 做更新的動作」。

原始的寫法如下：
1 用 ORM 將整張 table 讀入記憶體，每一 row 恰好對應一個物件。
2 跑迴圈，對物件做檢查，如果合乎條件，則做更新。

上述的寫法在資料量少的時候沒有影響，然而，在資料量大的時候，效能就會極差。因為多做了將整張 table 讀入記憶體的動作。比較好的重構版如下：

1    將要寫入 table 的資料，先寫入一張 temporary table。
例如：
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)

2.1 開啟 transaction
2.2 基於 temporary table 的值，用 join 操作來更新目的地的 table
例如：

UPDATE TABLE1
       JOIN TABLE2
       ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

2.3 關閉 transaction

3  丟棄 temporary table      

新的寫法，是將要用來寫入 table 的值先寫入資料庫裡，再透過 SQL 的指令去做資料的更新。如此，大量減少了記憶體與資料庫之間的資料搬移。對於數據量大的情況，就會有效能的大幅改進。

註：新的寫法中，其實可以不用加上 transaction ，因為只有一個 update 的操作。然而考慮實務上的程式，常常會有超過一個 update 的操作。當兩個 update 操作有必要緊接著完成，不可以在中間被其它的 session 插入讀取的動作，就會需要 transaction 。