How to do a sql update statement with a join?

Question

I'm trying to do an update statement with the sum of another column from another table. I want

customer_ar.current_bal to = sum(ar_transaction.current_balance)

As a test I ran this to make the customer_ar.current_bal=100 for 5 customers:

update customer_ar
set current_bal=100
where customer_id in (1502,1329,3096,1516,3605);

Next I ran this:

update customer_ar C
join ar_transaction T on T.customer_id=C.customer_id
set current_bal =
(select sum(current_balance)
from ar_transaction
where C.customer_id =T.customer_id)
where C.customer_id in (1502,1329,3096,1516,3605);

The result I got is that the balance for all of them became 213823.85. The actual current balance should all be different for each customer, and between 50 and 100.

I can do a subquery and it'll work if I do it one at a time, but I'm trying to see if there's a way to have it do all of them at once. Do you see what I'm doing wrong?

Answer 1

If you are using sql-server you can use this script.

update customer_ar 
set current_bal = 
    (select sum(current_balance)
        from ar_transaction T1
     where T1.customer_id =customer_ar.customer_id)
where customer_id in (1502,1329,3096,1516,3605);