How to Efficiently Update Table Records from a CTE in SQL?

Use CTE to efficiently update table records

The following code snippet shows how to efficiently update table records using common table expressions (CTE). The CTE CTE_DocTotal in the code calculates the PEDI_InvoiceDetail for each invoice by summarizing the Sale and VAT columns in the DocTotal table. The goal is to update the PEDI_InvoiceDetail column in the DocTotal table with the value calculated in the CTE.

However, the original code has a limitation: updates to the CTE are not automatically reflected to the source table (PEDI_InvoiceDetail). To achieve the desired results, the code needs to be modified.

Improvement plan:

The improved code uses a different CTE, named T, which retrieves InvoiceNumber, DocTotal, and a recalculated Sale based on the VAT and DocTotal values ​​for each invoice. This new DocTotal is evaluated using the OVER() clause and the PARTITION BY clause, ensuring that the evaluation is performed for each InvoiceNumber grouping.

The

UPDATE statement then uses the CTE T to assign the NewDocTotal value to the DocTotal column. This effectively updates the PEDI_InvoiceDetail column in the DocTotal table with the revised value of the CTE calculation.

The following is the modified code:

<code class="language-sql">WITH T AS
(
   SELECT  InvoiceNumber, 
            DocTotal, 
            SUM(Sale + VAT) OVER(PARTITION BY InvoiceNumber) AS NewDocTotal
    FROM    PEDI_InvoiceDetail
)
UPDATE  T
SET     DocTotal = NewDocTotal;</code>

The above is the detailed content of How to Efficiently Update Table Records from a CTE in SQL?. For more information, please follow other related articles on the PHP Chinese website!