Margaret Margaret - 3 months ago 35
SQL Question

Aggregate function in an SQL update query?

I'm trying to set the value in one table to the sum of the values in another table. Something along these lines:

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


Of course, as this stands, it won't work -
SET
doesn't support
SUM
and it doesn't support
GROUP BY
.

I should know this, but my mind's drawing a blank. What am I doing wrong?

Answer
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