Update records in table from CTE

sql

cte

sql-update

join

byNikita Barsukov·Oct 3, 2024

To update rows from a CTE, execute an INNER JOIN between the CTE and target table within the UPDATE statement. This fusion will provide the required SET operations as per the CTE output.

Example:

WITH DataCTE AS (
    SELECT ID, NewValue FROM SourceTable WHERE SomeCondition
)
UPDATE TargetTable
SET TargetColumn = DataCTE.NewValue
FROM DataCTE
WHERE TargetTable.ID = DataCTE.ID;

Key: Certify that the JOIN condition distinctly matches rows and averts unintended updates.

If you address total calculations, utilize the power of SUM() OVER() within the CTE and partition by consequential columns for exact totals. Apply this when managing invoices or any grouped data:

WITH TotalsCTE AS (
    SELECT InvoiceID, SUM(Amount) OVER (PARTITION BY InvoiceID) AS InvoiceTotal
    FROM InvoiceItems
)
UPDATE Invoices
SET DocTotal = TotalsCTE.InvoiceTotal
FROM TotalsCTE
WHERE Invoices.ID = TotalsCTE.InvoiceID;

Remember: Right partitioning translates to perfect totals for each grouping.

Mastering CTE-based updates

When handling tricky updates, it's paramount that your data manipulation is accurate. CTEs (Common Table Expressions) provides a readable and efficient way to canulate such logic before firing updates to the target table.

Dodge Common Pitfalls

Verify your column names and data types harmonize between the CTE and the target table. Misalignments here result in errors that block your SQL from executing:

Tip: Revisit your aliases and data types in both the CTE and the update clause.

Harness the Power of Joining

Correct syntax in the JOIN statement is a must-have for a successful CTE-based update. This not only includes matching column names but also being mindful about the type of JOIN that suits your case:

INNER JOIN: For updating rows existing in both tables. LEFT JOIN: For updating rows from the target table, match in the CTE or not.

Dynamic Totals with `SUM() OVER()`

The SUM() OVER() function paired with PARTITION BY allows you to compute aggregated values while keeping your result set intact. Ideal for maintaining detailed records and updating summary data:

Use case: Adding a running total or windowed aggregate to your data before hitting the main table with an update.

Example:

WITH RunningTotalsCTE AS (
    SELECT InvoiceID, LineItem, Amount, 
           SUM(Amount) OVER(PARTITION BY InvoiceID ORDER BY LineItem) AS RunningTotal
    FROM InvoiceLines
)
UPDATE Invoices
SET RunningTotal = RunningTotalsCTE.RunningTotal
FROM RunningTotalsCTE
WHERE Invoices.ID = RunningTotalsCTE.InvoiceID 
      AND Invoices.LineItem = RunningTotalsCTE.LineItem;