0

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 ?

3
Contributors
3
Replies
5
Views
5 Years
Discussion Span
Last Post by bilal_fazlani
1

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,

Votes + Comments
simply awessome.!
1

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

Edited by adam_k: n/a

Votes + Comments
greaaaat..!
0

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

it worked..

thanks a lot guys..

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.