Sql Update query with group by clause

sql

subquery

update-query

group-by

byAnton Shumikhin·Aug 22, 2024

UPDATE t1
SET t1.Total = t2.SumAmount
FROM Orders t1
INNER JOIN (
    SELECT CustomerID, SUM(Amount) AS SumAmount
    FROM Orders
    GROUP BY CustomerID
) t2 ON t1.CustomerID = t2.CustomerID

This SQL snippet updates the Total field in the Orders table with the sum of Amount for each customer. Be sure to match your column names with those in your actual table. This unique recipe combines an aggregate function (SUM) within a subquery with a JOIN to apply a grouped result to an update operation.

Efficiently delivering updates with INNER JOIN

Let's take things up in our INNER JOIN game when constructing UPDATE queries with GROUP BY. The efficient use targets only the rows satiating the condition, minimizing effort, and improving performance.

Consider rustling up an UPDATE query for rows having the maximum age within each type, your key ingredients would be a subquery and an INNER JOIN:

-- ☕️ Grab a java, buckle up and prepare for "high" octane performance
UPDATE YourTable
SET Name = 'HIGH'
FROM YourTable AS t1
INNER JOIN (
    SELECT Type, MAX(Age) AS MaxAge
    FROM YourTable
    GROUP BY Type
) AS t2 ON t1.Type = t2.Type AND t1.Age = t2.MaxAge

Essentially, we group by Type to break down data, max out the Age, and then push the pedal to the metal by updating Name to 'HIGH'. Precision, thy name is SQL.

Juggling complex conditions with subqueries

Squeeze extra mileage out of subqueries by handling complex conditions in UPDATE statements. Use a nested subquery to compute and compare aggregated data, then hit the update button. Magic 🧙‍♂️

UPDATE multiple rows based on such tricky conditions? Not a problem:

-- 🕰 Time to rise and shine, senior staff!
UPDATE Employees
SET Status = 'Senior Staff'
WHERE Age > (
    SELECT AVG(Age) FROM Employees
)

We've just promoted Employees elder than the average age, handled with a finesse only a subquery can offer.

Setting your aim right in the UPDATE query

Marksmanship in queries–crucial for updates–is achieved by accurately using SET. Always define the table name when setting new values, more so when handling multiple tables:

-- 💰 Time to meet your SalesQuota folks!
UPDATE SalesForce
SET SalesForce.SalesQuota = DepartmentGoals.GoalAmount
FROM SalesForce
JOIN DepartmentGoals ON SalesForce.DepartmentID = DepartmentGoals.DepartmentID

Absolute clarity in SalesForce.SalesQuota helps dodge the bullet of ambiguity and ensures successful execution.

Avoiding SQL snares

Even the best of us can step on a few SQL landmines. For instance, using "having" minus "group by" tends to rig a faulty query structure, leading to a botched up output:

-- 💲 Sales over 10K? You happen to land on SQL Fortune 500 list!
SELECT EmployeeID, SUM(Sales)
FROM SalesGroup
GROUP BY EmployeeID
HAVING SUM(Sales) > 10000

When faced with the option of updating aggregates directly, dodge the bait! Use them in subqueries to specify the update:

-- 📉 The Price is Right! Apply a 10% discount to products with over 100 sales 
UPDATE Products
SET Products.Price = Products.Price * 0.9
FROM Products
WHERE EXISTS (
    SELECT 1 FROM Sales
    WHERE Sales.ProductID = Products.ProductID
    GROUP BY Sales.ProductID
    HAVING COUNT(Sales.ProductID) > 100
)