Update table based on sum from another table...

[omacron]

I am trying to write a update script and having some problems. Let me show you the an example;

Data;

Table:  Payments
CustName       Amount   
Bob            10.00
Bob            45.00
Sam             5.00
Sam            85.00
Bob            20.00

Now what I need to do is update a Customer table with the payments. So this what the results should be;

Table:  Customer
CustName      Payment
Bob            75.00
Sam            90.00

I am really not sure where to start for this. I know how to do the update if it wasn't a sum;

update Customer
  set Customer.Payment = P.Amount
  from Customer C
  inner join Payments P
  on P.CustName = C.CustName

Thanks for the help

 

[gmmastros]

Try this...

update C
Set    C.Payment = A.SumOfAmount
from   Customer C
       Inner Join (
         Select Sum(Amount) As SumOfAmount,
                CustName
         From   Payments
         Group By CustName
         ) As A
         on A.CustName = C.CustName

If this works for you, and you'd like me to explain it, just let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[omacron]

thanks that worked perfectly. I kept thinking it needed to do if statements etc... Basically kept making it more complicated then it needed to be.