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?

Solution 1

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

Solution 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 ...

Solution 3

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

Solution 4

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

View more solutions

Comments

[Etienne]

I have chosen the CTE option for better performance.

[Zack]

But what is the correct way to do this? Which is faster? Which is more readable?

[podiluska]

@Zack "Correctness" and "readability" in SQL are to some extent subjective and a matter of taste. However, some historic versions of SQL server don't contain the CTE feature.

[Holger Jakobs]

SQLite3 3.8.11.1 complains: Error: near "FROM": syntax error

[podiluska]

@HolgerJakobs This question is about SQL Server. SQLite will have its own syntax

[Binu Vijayan]

Is CTE UPDATE atomic , if run concurent queries will the same records updated twice ?

[GarethD]

A CTE update is no different at all from a normal update. It is atomic and will take the same table/row locks. It is nothing more than syntactic sugar

[Derek Greer]

The question is about how to update records from CTE, not about alternate approaches. There are several reasons the OP may have wanted to use a CTE which aren't revealed in the question.

[podiluska]

@DerekGreer The OP says "I want to enter into the column the DocTotal value inside PEDI_InvoiceDetail." He doesn't say he needs or wants to necessarily use a CTE.

[Derek Greer]

Correct, except for the fact that he summarizes his question in the title: "Update records in table from CTE". As evidence I'm reading the OP's desire for an answer that involves a CTE, I submit as evidence that he accepted an answer providing a solution using a CTE and that he stated in a follow up comment that he chose a CTE for performance.

[Fandango68]

Actually updating the same table based on a query of that table will break concurrency and force the query to run twice. Once, correctly, and finally with the final value which is the last record of the subquery. Try it