mysql - 在 UPDATE 的 WHERE 条件下引用 CTE

Question

我正在尝试运行这样的查询：

WITH cte AS
(
  SELECT id, category, SUM(amount) AS sum_amount FROM t1 GROUP BY id, category
)
UPDATE table SET amount = cte.sum_amount WHERE id = cte.id;

但是，我不断收到错误

Unknown column 'cte.id in WHERE clause'

有谁知道我如何在 UPDATE 查询中引用我的公用表表达式，或者重写它？

标签： mysqlsqlcommon-table-expression

Answer 1

你可以试试下面

WITH cte AS
(
  SELECT id, SUM(amount) AS sum_amount FROM t1 GROUP BY category
)
UPDATE T
SET  T.sum_amount= CT.sum_amount
FROM table T
JOIN cte CT
     ON T.id = CT.id