Answered by:
StoredProcedure--Sum(TableA) to Update Amount in TableB

Question
-
User-1497429768 posted
Hi,
The StoredProcedure is as follows-I want to sum from TableA into TableB
update TableB set amount=A.AMOUNT
from (select sum(IsNull(amount,0)) as amount from TableA where no=@no) A
where no=@noIt can work fine.But if the TableA is empty,the StoredProcedure can't work. so,how to fix that. Thanks.
jeff
Wednesday, May 31, 2017 9:53 AM
Answers
-
User77042963 posted
Merge TableB tgt Using(select no, Isnull(sum(amount),0) as amount from TableA where no=@no Group by no) src On tgt.no=src.no When Matched then update set amount=src.AMOUNT ;
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, May 31, 2017 3:00 PM
All replies
-
User-1509636757 posted
But if the TableA is empty,the StoredProcedure can't work. so,how to fix that.do you face any error when TableA is empty? I try to generate similar scenario with table variable and I do not see any issue when TableA is empty:
declare @TableB table (amount decimal, no int) declare @TableA table (amount decimal, no int) declare @no int = 1 --insert into @TableA select 100,1 --insert into @TableA select 200,1 --insert into @TableB select null,1 update @TableB set amount=A.AMOUNT from (select sum(IsNull(amount,0)) as amount from @TableA where no=@no) A where no=@no select * from @TableB
Wednesday, May 31, 2017 10:50 AM -
User-1497429768 posted
Hi kaushalparik27,
When I delete TableA,then the TableA is empty.In the meantime,after running the StoredProcedure,I found the result of the TableB is the same value(not zero). So something is not correct.
Hope can give me any suggestion.Thanks.
jeff
Wednesday, May 31, 2017 2:17 PM -
User77042963 posted
Merge TableB tgt Using(select no, Isnull(sum(amount),0) as amount from TableA where no=@no Group by no) src On tgt.no=src.no When Matched then update set amount=src.AMOUNT ;
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, May 31, 2017 3:00 PM -
User-1497429768 posted
Hi limno,
It is very helpful to me.But I still encounter one problem--If the TableA is empty,I hope the amount of field in TableB is zero. I try to modify your code as follows:
Merge TableB B
Using (select no,sum(IsNull(amount,0)) as amount from TableA where no=@NO group by no) A on A.no=B.no
when Matched then update set B.amount=A.AMOUNT
when not Matched then update set B.amount=0;A syntax error is thrown. Have any idea? I research all day and can't find any solution.
Thank you for your help.
jeff
Thursday, June 1, 2017 2:49 AM -
User-1497429768 posted
Hi limno,
I use another method,and it work. So Thanks again.
IF exists(select no from TableA where no=@NO) begin
update TableB set amount=A.AMOUNT
from (select sum(IsNull(amount,0)) as amount from TableA where no=@NO) A
where no=@NO
end
else begin
update TableB set amount=0 where no=@NO
endbest regards,
jeff
Thursday, June 1, 2017 7:01 AM