locked
StoredProcedure--Sum(TableA) to Update Amount in TableB RRS feed

  • 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=@no

    It 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
    end

    best regards,

    jeff

    Thursday, June 1, 2017 7:01 AM