mysql - 使用 SQL INNER JOIN 批量更新字段

Question

我有两个表 Invoices 和 payments。发票有很多付款，我想为所有发票行更新名为 total_payments_amount 的字段，并在单个 SQL 查询中为每个行更新所有关联付款的总和。所以这是我尝试过的

---

UPDATE invoices
SET total_payments_amount = pays.total_amount
FROM invoices inv
INNER JOIN (
  SELECT invoice_id, SUM(amount) as total_amount
  FROM payments
  WHERE invoice_id IS NOT NULL
  GROUP BY invoice_id
) pays
ON inv.id = pays.invoice_id

但运行此查询后，我发现所有记录都包含相同的值。那么这里出了什么问题？

Answer 1

尝试使用 cte..

;With cte_total
AS ( SELECT invoice_id, SUM(isnull(amount,0)) as total_amount
       FROM payments
       WHERE invoice_id IS NOT NULL
      GROUP BY invoice_id)
UPDATE inv
   SET inv.total_payments_amount = c.total_amount
FROM cte_total c
     JOIN invoices inv 
        ON inv.id = c.invoice_id