Update MySQL Table Field With Value Derived From Another Table

If you need to update certain field in a table with value derived from another table (e.g. performing a sum), you could do this using a JOIN statement in the following way (which I referred from this post):

UPDATE table1 a
JOIN
   ( SELECT batch_no, SUM(sale_amt) AS sum_sale_amt
     FROM table2
     WHERE source = 'SALE'
     GROUP BY batch_no
   ) b ON 
   b.batch_no = a.batch_no
SET a.total_sale_amt = b.sum_sale_amt
WHERE a.cancel <> 1;

In the above example, we sum the `sale_amt` from `table2` where the `source` is 'SALE', grouped by `batch_no`, into an alias `sum_sale_amt`. Then we update this sum into `table1`'s field `total_sale_amt` based on the `batch_no`. This update only applies to `table1` records which is not cancelled.

This query can be run multiple-times to update/fix any inconsistency error when saving from the derived values.

If you find this post helpful, would you buy me a coffee?