Update records in table from CTE

Question

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

User · Accepted Answer

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

User · Answer

You don t need a CTE for this  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

User · Answer

Try the  following query    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

User · Answer

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

[sql] Update records in table from CTE

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to common-table-expression