update query

[bilal_fazlani]

I have tried this :

update customers set customers.acc_balance = (select sum (invoices.inv_amount) from customers left join invoices on customers.cust_id=invoices.cust_id   group by customers.cust_id)

this

update customers set customers.acc_balance = (select sum (invoices.inv_amount) from customers inner join invoices on customers.cust_id=invoices.cust_id   group by customers.cust_id)

error is :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

when i try this :

this

update customers set customers.acc_balance = (select sum (invoices.inv_amount) from customers left join invoices on customers.cust_id=invoices.cust_id   group by customers.cust_id) where invoices.cust_id = customers.cust_id

error is :

The multi-part identifier "invoices.cust_id" could not be bound.

anyone knows what to do next ?

[MeSampath]

Hi,

Change the query like this;

UPDATE customers SET acc_balance = (SELECT sum (isnull(I.inv_amount,0)) FROM invoices I left join customers AA on AA.cust_id=I.cust_id where customers.cust_id=AA.cust_id GROUP BY AA.cust_id)

This should work.

Thank you,

[adam_k]

This is the way to go:

update customers 
set customers.acc_balance = a.inv_sum 
from customers left join 
(select cust_id, sum(inv_amount) as 'inv_sum' from invoices
group by cust_id) 'a'
on customers.cust_id = a.cust_id

[bilal_fazlani]

you people are magicians ..
I just copied it and pasted..

it worked..

thanks a lot guys..