How Can I Update Table Records with Calculated Values from a CTE?

Efficiently Updating Table Records Using CTEs

This article explains how to correctly update table records using a Common Table Expression (CTE) to calculate new values. The goal is to update the DocTotal column in the PEDI_InvoiceDetail table with a recalculated total. A previous attempt failed because updates to the CTE didn't propagate to the base table.

The Challenge

Directly updating a table based on a CTE's calculated values often leads to errors. The CTE acts as a temporary result set; changes within it don't automatically update the underlying table.

The Solution

The solution involves a two-step process using a CTE:

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

This revised query:

1. Creates a CTE: The CTE T selects InvoiceNumber, the original DocTotal, and calculates a new DocTotal (NewDocTotal) using the SUM() OVER (PARTITION BY InvoiceNumber) function. This efficiently aggregates Sale VAT for each unique InvoiceNumber.

2. Updates the Base Table: The UPDATE statement then directly targets the CTE T, setting the original DocTotal to the newly calculated NewDocTotal. This ensures the changes are applied to the PEDI_InvoiceDetail table.

Key Concepts

• OVER (PARTITION BY ...): This crucial clause partitions the data by InvoiceNumber, allowing the SUM() function to calculate the total for each invoice separately.

This approach provides a clean and efficient method for updating records with calculated values derived from a CTE, avoiding the common pitfalls of trying to modify the CTE itself.

The above is the detailed content of How Can I Update Table Records with Calculated Values from a CTE?. For more information, please follow other related articles on the PHP Chinese website!