Sql Update to the SUM of its joined values

sql

subqueries

aggregates

best-practices

byNikita Barsukov·Jan 10, 2025

To update a column with the cumulative values from another table, we apply a subquery with INNER JOIN, akin to a swift surgery performed with a scalpel:

UPDATE main
SET total = (
    SELECT SUM(amount)
    FROM details
    INNER JOIN main ON main.id = details.main_id
    GROUP BY details.main_id
);

Note: Here 'main' is your patient table, 'total' is the target column, 'details' is the store of values, and 'main_id' is your trusty scalpel, carving out the relationship between data points.

Dissecting Subqueries and Aggregates

Ever tried putting a square peg in a round hole? That's what embedding aggregates like SUM() in a SET clause feels like to SQL. We use a subquery as a buffer to accommodate this discrepancy.

Aggregate Functions in Subqueries

SQL might respond to direct use of aggregate functions in a SET clause like a teenager asked to clean their room – flat-out refuse. To circumvent this, we neatly wrap the aggregate function within a subquery:

UPDATE main
SET total = (
    SELECT SUM(details.amount)
    FROM details
    WHERE details.main_id = main.id
    ...

Grouping – Divide and Conquer

Grouping is crucial to ensure we are not gluing the treasurer's accounts to your shopping list. So, we use GROUP BY within the subquery to keep the finances separate:

...
    GROUP BY details.main_id
);

CTEs – The VIP of SQL

For SQL queries equivalent to literary epics, Common Table Expressions (CTEs) offer a readable and efficient approach, almost like using bookmarks:

WITH SumDetails AS (
    SELECT main_id, SUM(amount) AS TotalAmount
    FROM details
    GROUP BY main_id
)
UPDATE main
SET total = (
    SELECT TotalAmount
    FROM SumDetails
    WHERE SumDetails.main_id = main.id
);

Note: Ensure your SQL Server version is in the CTE fanclub before joining.

Best Practices: SQL Do's and Don'ts

Here are a few pro-tips to avoid turning an SQL operation into a classic 'Oops!' moment.

Key Matching - Don't Mix Apples and Oranges

Ensure you're matching the keys accurately in your JOIN condition. A mistake here can cause more chaos than mixing up identical twins:

...
INNER JOIN main ON main.id = details.main_id
...

Update Preview – Window Shopping Before Purchase

Before diving headfirst into the UPDATE, ensure it's the change you want. How? By previewing the results with a similar SELECT statement, like trying on clothes before purchasing:

SELECT main.id, main.total, (SELECT SUM(amount) FROM details WHERE details.main_id = main.id) AS new_total
FROM main
INNER JOIN details ON main.id = details.main_id
GROUP BY main.id;