本文介绍了从 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
现在有了这个结果,我想在列中输入 PEDI_InvoiceDetail 中的 DocTotal 值.
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 更新表中的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!