How Can I Update a Table with the Sum of Values from Another Table in SQL?

Aggregating in SQL UPDATE Queries: A Concise Solution

In database operations, updating table values based on aggregate functions can pose a challenge. Let's explore a common scenario where the goal is to set the value in one table to the sum of values in another table.

Consider the following query:

UPDATE table1
SET field1 = SUM(table2.field2)
FROM table1
INNER JOIN table2 ON table1.field3 = table2.field3
GROUP BY table1.field3

While intuitive, this query will fail due to two key constraints: SET does not support aggregate functions like SUM, and GROUP BY is not permitted in UPDATE queries.

To resolve this issue, we can utilize a subquery to calculate the aggregate sum and then use the updated value in the UPDATE query.

UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum from table2 group by field3) as t2
on t2.field3 = t1.field3

In this refined query:

• The subquery calculates the aggregate sum for each unique value in table2.field3.
• The updated result, stored as field2Sum, is joined back to table1 using the common column field3.
• The JOIN condition ensures that the SUM value from table2 is assigned to the correct corresponding row in table1.

By employing this approach, you can successfully perform aggregate updates in SQL and achieve the desired data manipulation results.

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