指更新条件为where的cte

k5ifujac 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(443)

我试图运行这样的查询：

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查询中引用公共表表达式，或者重写它吗？

sql mysql common-table-expression

来源：https://stackoverflow.com/questions/53591604/referring-to-cte-in-where-condition-of-update

2条答案

按热度按时间

myss37ts1#

使用临时表的替代方法，您可以阅读有关cte和临时表的信息
临时表：

SELECT id, category, SUM(amount) AS sum_amount 
INTO #temp
FROM t1 GROUP BY id, category

使用临时表更新查询：

UPDATE OT
SET  OT.sum_amount= TT.sum_amount
FROM table OT
JOIN #temp TT
    ON OT.id = TT.id

cte:

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

赞(0）回复(0）举报 2021-06-18

dojqjjoe2#

你可以在下面试试

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

赞(0）回复(0）举报 2021-06-18

我来回答

指更新条件为where的cte

2条答案

相关问题

热门标签

最新问答