Update one table based upon SUM(values) in another table on multiple criteria

Try this solution:

UPDATE m
SET m.Foo = f.valsum
FROM [MASTER] m
INNER JOIN
(
  SELECT ID, CCY, SUM(val) valsum
  FROM Foos
  GROUP BY  ID, CCY 
) f ON m.ID = f.ID AND m.CCY  = f.CCY;

With postgres, I had to adjust the solution with this to work for me:

UPDATE [MASTER] m
SET Foo = f.valsum
FROM 
(
  SELECT ID, CCY, SUM(val) valsum
  FROM Foos
  GROUP BY  ID, CCY 
) f
WHERE m.ID = f.ID AND m.CCY = f.CCY;

Tags:

Sql

Tsql

Sql Update

Related