need a sum on an update without using a derived table

[WalterHeisenberg]

Hi guys,

I have a stored procedure which is getting a list of IDs that need to have data populated from various tables. I'm creating a temp table and then using updates to get those values. An example of one update statement is:

update #ATemp 
set  AMT = AMTTOT
from  H h
join D d on ...
join C c on ...
join O o on ...
join V v on ...
join #ATemp a on ... --filter to original list we're updating

where ...

I've made it ambiguous for brevity but the heart of the matter is this: AMTTOT has 2 rows being returned but the amount displayed when the SP is complete only shows the value of one row. There are many other columns being updated from the various joined tables and many update statements so ideally I'm thinking/hoping there is a way to sum that AMTTOT field without resorting to using a derived table.

I tried something along the lines of AMT = isnull(AMT,0) + AMTTOT but that didn't work and I can't add in a sum() as I get an error I can't have an aggregate in a set.

Is there a way to get this to happen? I'm not looking forward to having to rewrite a bunch of updates if I can help it. As always your help is greatly appreciated in advance.

Thanks.

 

[markros]

I think joining with the derived table doing aggregates is the best and simplest solution for the problem.

PluralSight Learning Library

 

[WalterHeisenberg]

Hello - I've done so to get this finished but could I have done it another way? I feel like there was another way to do it.

 

[markros]

Another way will be using CROSS APPLY, but I think JOIN with the derived table using the SUM is the cleanest approach.

PluralSight Learning Library