Alex Rivera | Logout

Update records in table from CTE

Asked 2012-07-19T14:01:07.997
100

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

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?

sql sql-server t-sql common-table-expression
Edit
Report

1 Answer

55
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 ...
answered 2012-07-19T14:02:58.117

Your Answer