从CTE更新表中的记录

Question

我有以下的CTE，将给我整个发票的DocTotal。

 ;WITH CTE_DocTotal
 AS
 (
   SELECT SUM(Sale + VAT) AS DocTotal
   FROM PEDI_InvoiceDetail
   GROUP BY InvoiceNumber
 )

UPDATE PEDI_InvoiceDetail
SET DocTotal = CTE_DocTotal.DocTotal

现在我想要将PEDI_InvoiceDetail中的DocTotal值输入到列中。

我知道这样不起作用，我也知道我漏掉了什么，但是我缺少了什么呢？

- Etienne

---

我选择了CTE选项以获得更好的性能。 - Etienne

Answer 1

您对CTE所做的更新将会被级联到源表中。

我稍微猜测了一下您的模式，但是像这样的东西应该可以工作。

;WITH T AS
(   SELECT  InvoiceNumber, 
            DocTotal, 
            SUM(Sale + VAT) OVER(PARTITION BY InvoiceNumber) AS NewDocTotal
    FROM    PEDI_InvoiceDetail
)
UPDATE  T
SET     DocTotal = NewDocTotal

- GarethD

---

CTE UPDATE 是原子的吗？如果同时运行查询，是否会更新相同的记录两次？ - Binu Vijayan

CTE更新与普通更新没有任何区别。它是原子性的，会获取相同的表格/行锁定。它只是语法糖，没有更多的含义。 - GarethD

@GarethD - MERGE语句不是原子性的，而且CTE的MSFT文档页面也没有提到原子性 - 你有什么证据支持这个说法吗？ - StingyJack

@StingyJack 这个问题与 MERGE 没有任何关系，所以不确定 MERGE 不是原子操作的相关性。至于 CTE，我找不到确切的参考资料说明它们是原子的，但我在 UPDATE 的文档中也找不到这个。如上所述，CTE 是语法糖，编译器会将其重写为普通的更新，因此如果普通的更新是原子的，那么引用 CTE 的更新也是原子的。在证据方面，我能做的最好的事情就是向您展示两者的示例（db<>fiddle），以证明它们具有完全相同的执行计划。 - GarethD

我提到了MERGE，因为许多人认为MERGE语句是原子的，但实际上它们并不是。在CTE很简单的情况下，它可能总是只是UPDATE的语法糖。我不知道当CTE嵌套或使用其他表中的数据时是否也可以这样说，文档中也没有说明。 - StingyJack

Answer 2

WITH CTE_DocTotal (DocTotal, InvoiceNumber)
AS
(
    SELECT  InvoiceNumber,
            SUM(Sale + VAT) AS DocTotal
    FROM    PEDI_InvoiceDetail
    GROUP BY InvoiceNumber
)    
UPDATE PEDI_InvoiceDetail
SET PEDI_InvoiceDetail.DocTotal = CTE_DocTotal.DocTotal
FROM CTE_DocTotal
INNER JOIN PEDI_InvoiceDetail ON ...

- Paul Fleming

Answer 3

你不需要使用公共表表达式来实现这个功能

UPDATE PEDI_InvoiceDetail
SET
    DocTotal = v.DocTotal
FROM
     PEDI_InvoiceDetail
inner join 
(
   SELECT InvoiceNumber, SUM(Sale + VAT) AS DocTotal
   FROM PEDI_InvoiceDetail
   GROUP BY InvoiceNumber
) v
   ON PEDI_InvoiceDetail.InvoiceNumber = v.InvoiceNumber

- podiluska

---

"正确性"和"可读性"在SQL中在一定程度上是主观的，也是品味的问题。然而，一些历史版本的SQL服务器并不包含CTE功能。 - podiluska

@HolgerJakobs 这个问题是关于 SQL Server 的。SQLite 会有自己的语法。 - podiluska

问题是如何从CTE更新记录，而不是关于其他方法的问题。 在问题中没有透露出OP可能想使用CTE的几个原因。请注意，我已经尽力将翻译变得简洁易懂，并保持了原文的意思，但并未添加任何解释或额外内容。 - Derek Greer

楼主说，“我想将PEDI_InvoiceDetail中的DocTotal值输入到列中。”他并没有说他需要或想要必须使用一个CTE。 - podiluska

正确，除了他在标题中总结问题：“从CTE更新表中的记录”这一事实。我认为他希望得到涉及CTE的答案的证据是，他接受了一个使用CTE提供解决方案的答案，并在后续的评论中表示他选择CTE来提高性能。请注意，这是一份翻译文件，请勿添加任何注释或其他信息。 - Derek Greer

Answer 4

尝试以下查询：

;WITH CTE_DocTotal
 AS
 (
   SELECT SUM(Sale + VAT) AS DocTotal_1
   FROM PEDI_InvoiceDetail
   GROUP BY InvoiceNumber
 )

UPDATE CTE_DocTotal
SET DocTotal = CTE_DocTotal.DocTotal_1

- user2063329

---

我相信这会导致"无效的列名DocTotal"，因此你可能需要在CTE的SELECT语句中添加DocTotal。 - undefined