Aggregate function in an SQL update query?
SqlSql ServerTsqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
Use:
UPDATE table1
SET field1 = (SELECT SUM(t2.field2)
FROM TABLE2 t2
WHERE t2.field3 = field2)
Solution 3 - Sql
Or you could use a mix of JBrooks and OMG Ponies answers:
UPDATE table1
SET field1 = (SELECT SUM(field2)
FROM table2 AS t2
WHERE t2.field3 = t1.field3)
FROM table1 AS t1
Solution 4 - Sql
A 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
Solution 5 - Sql
I know the question is tagged SQL Server but be careful with UPDATE with JOIN if you are using PostgreSQL. @JBrooks answer won't work :
UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (...) as t2
on t2.field3 = t1.field3
You will have to adapt it to :
UPDATE table1 t1
SET t1.field1 = t2.field2Sum
FROM (...) as t2
WHERE t2.field3 = t1.field3
See parameter from_list
in the doc to get why FROM
is considered by PostgreSQL as a self-join : https://www.postgresql.org/docs/9.5/static/sql-update.html#AEN89239
Solution 6 - Sql
You can also use CTE like below.
;WITH t2 AS (
SELECT field3, SUM(field2) AS field2
FROM table2
GROUP BY field3
)
UPDATE table1
SET table1.field1 = t2.field2
FROM table1
INNER JOIN t2 ON table1.field3 = t2.field3