BlueChippy BlueChippy -4 years ago 98
SQL Question

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

Apols, 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 Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download