Aggregate function in SQL update query?

Question

I am trying to set a value in one table to the sum of the values ​​in another table. Something like that:

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

Of course, since it's worth it, it won’t work - SET does not support SUM and does not support GROUP BY .

I should know this, but my mind draws a space. What am I doing wrong?

Answer 1

 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 

Answer 2

Using:

 UPDATE table1 SET field1 = (SELECT SUM(t2.field2) FROM TABLE2 t2 WHERE t2.field3 = field2) 

Answer 3

Or you can use a combination of JBrooks and OMG Ponies :

 UPDATE table1 SET field1 = (SELECT SUM(field2) FROM table2 AS t2 WHERE t2.field3 = t1.field3) FROM table1 AS t1 

Answer 4

Good situation to use CROSS APPLY

 UPDATE t1 SET t1.field1 = t2.field2Sum FROM table1 t1 CROSS APPLY (SELECT SUM(field2) as field2Sum FROM table2 t2 WHERE t2.field3 = t1.field3) AS t2