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

I can’t seem to find out how to do this and not sure exactly how to search for it!

I have a table [MASTER]:

ID varchar(6)
CCY varchar(3)
Val1 decimal(20,5)
Val2 decimal(20,5)
FOO decimal(20,5)

and another table [FOOS]

ID varchar(6)
CCY varchar(3)
Val decimal(20,5)

MASTER contains one row per ID/CCY composite key (not sure if thats correct term) e.g.

ABCDEF GBP 200.00 100.00 null
ABCDEF EUR 400.00 150.00 null
ZYXWVU GBP 300.00 200.00 null
ZYXWVU EUR 400.00 200.00 null

FOOS contains multiple rows and DOES NOT contain a row for every MASTER e.g.

ABCDEF GBP 50.00
ABCDEF GBP 51.00
ABCDEF GBP 150.00
ZYXWVU GBP 100.00
ZYXWVU EUR 200.00
ZYXWVU EUR 400.00

I’d like to run a query to update only matching MASTER rows with SUM(FOOS.Val). e.g.

ABCDEF GBP 200.00 100.00 251.00
ABCDEF EUR 400.00 150.00 null
ZYXWVU GBP 300.00 200.00 100.00
ZYXWVU EUR 400.00 200.00 600.00

…but although I’ve tried a numer of options (where exists, inner join) I can’t seem to be able to either link to a single MASTER or do the SUM(...)

Answer

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;

Advertisement

Answer