116

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Margaret
  • 5,749
  • 20
  • 56
  • 72

6 Answers6

169
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
JBrooks
  • 9,901
  • 2
  • 28
  • 32
  • 46
    I put the three queries side-by-side and ran an execution plan. This answer had a cost of 5%. – Margaret Jan 06 '10 at 00:24
  • 1
    Elegant, easy to implement...Where have you been all day??? I have been banging my head over it for more than an hour now :) – Ange1 Jan 07 '16 at 19:43
  • 1
    Important: Watch out if any of the fields you are grouping by might be nullable (eg. field3 above). you'd need to modify the 'join' to account for this or your sums will be inaccurate (https://stackoverflow.com/a/14366034/16940) – Simon_Weaver Nov 09 '18 at 10:07
  • This is not working in postgresql. You must do it like in this post here: https://stackoverflow.com/questions/35393706/postgresql-update-using-aggregate-function – M46 Apr 09 '21 at 12:00
13

Use:

UPDATE table1
   SET field1 = (SELECT SUM(t2.field2) 
                   FROM TABLE2 t2 
                  WHERE t2.field3 = field2)
Community
  • 1
  • 1
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 16
    I put the three queries side-by-side and ran an execution plan. This answer had a cost of 44%. – Margaret Jan 06 '10 at 00:24
  • this didn't work for me, because t2.filed3 was the same name as table1.field2, so the join performed behind the scenes didn't work properly. (I assuem there's a join behind the scenes) – Joe Aug 02 '19 at 15:06
7

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
Jonathan Roberts
  • 416
  • 4
  • 7
  • CROSS APPLY is elegant way and you can populate multiple columns as well. – huhu78 Oct 31 '22 at 12:14
  • I suggest to rename "t2" alias (CROSS APPLY product) into "f_sum" as you're applying a function against "t1" table. Using "t2" both as table2 alias and CROSS APPLY product alias can be misleading for the readers. – huhu78 Oct 31 '22 at 12:20
6

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
Community
  • 1
  • 1
Paulo Santos
  • 11,285
  • 4
  • 39
  • 65
4

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

Bludwarf
  • 824
  • 9
  • 21
3

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
Karan
  • 12,059
  • 3
  • 24
  • 40
  • I used to do the update with subquery till I faced a situation today where subquery table needed outer table for a join. This is what works best in that case. – KSK Jun 24 '21 at 04:02