How to Update a Table Field with the Sum from Another Table Using SQL?

SQL Update Query with Aggregate Function

This question explores the use of aggregate functions in SQL update queries. The user attempts to update a field in a table with the sum of values from another table. However, their query does not work due to syntactical issues.

Analysis

The initial query presented by the user contains two main flaws:

1. Mixing of SET and GROUP BY clauses: SET does not support GROUP BY, and vice versa.
2. Direct use of an aggregate function (SUM) in the SET clause: SET expects a single value, not an aggregation.

Solution

To resolve these issues, a subquery must be employed. The subquery will compute the sum of values from the second table, grouping by the common field. The result of this subquery will then be joined with the first table and used to update the field accordingly.

The correct 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 query:

• The subquery calculates the sum of field2 from table2 for each unique value of field3.
• The outer query joins table1 with the subquery result using the field3 field.
• The field1 of table1 is updated with the field2Sum computed in the subquery.

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