Margaret Margaret - 1 year ago 137
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 -
doesn't support
and it doesn't support

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

Answer Source
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  
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download