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

Question

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…

Accepted 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;
```

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

Solution

Problem