从CTE更新表中的记录

问题描述

我有以下CTE，可以为我提供整个发票的DocTotal。

I have the following CTE that will give me the DocTotal for the entire invoice.

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

UPDATE PEDI_InvoiceDetail
SET DocTotal = CTE_DocTotal.DocTotal

现在有了此结果，我想在DocTotal值中输入列在PEDI_InvoiceDetail中。

Now with this result I want to enter into the column the DocTotal value inside PEDI_InvoiceDetail.

我知道这行不通，我想念什么，这是什么？

I know is not going to work and I know I am missing something, what is it?

推荐答案

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

Updates you make to the CTE will be cascaded to the source table.

我不得不稍微猜测一下您的架构

I have had to guess at your schema slightly, but something like this should work.

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

这篇关于从CTE更新表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！