I know there are more ways to solve this problem (I can think of at least
2 more) but I think the easiest approach is to break this into two stages.
First stage, we compute the sums of the purchases and the credits:
SELECT @purchases := SUM(amount_of_credit_used)
FROM purchases
WHERE customer_id='jdoe';
SELECT @deposits := SUM(Amount)
FROM deposits
WHERE customer_id='jdoe'
Then we can do the update to the customers table
UPDATE customers
SET balance = @[EMAIL PROTECTED]
WHERE customer_ID = 'jdoe';
A second method is to perform two UPDATES in sequence:
UPDATE customers c
INNER JOIN purchases p
on c.customer_ID = p.customer_ID
SET c.balance = SUM(p.amount_of_credit_used)
WHERE c.customer_ID = 'jdoe';
UPDATE customers c
INNER JOIN deposits d
on c.customer_ID = d.customer_ID
SET c.balance = c.balance + SUM(d.amount)
WHERE c.customer_ID = 'jdoe';
The first update replaces the old value in customers.balance with the
"credit used" total while the second update builds on the first value.
Do either of these approaches make sense for you?
I don't know how busy your database is but for a real-world application I
would either lock the tables to prevent outside updates during the
computation, or I would wrap the whole process with a transaction. That
way you can avoid updating the balance with only part of the information.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Rob Best <[EMAIL PROTECTED]> wrote on 07/23/2004 07:24:02 AM:
> I hope this isn't a stupid question but this is my best place to ask...
>
> I have a database where I need to do a sum of a returned select...
>
> SELECT SUM(purchases.amount_of_credit_used) WHERE customer_id="jdoe";
>
> and do a sum of another returned select...
>
> SELECT SUM(amount) from deposits WHERE customer_id="jdoe";
>
>
> So far so good. Above two statements work fine.
> The problem is I need to two sums added together and put into a third
> table/field (customers.balance).
> shorthand I would have expected the sql statement to look something
> like...
>
> UPDATE customers SET balance=(
> SUM(
> SELECT SUM(purchases.amount_of_credit_used) WHERE
customer_id="jdoe",
> SELECT SUM(amount) from deposits WHERE customer_id="jdoe"
> )
> ) WHERE customer_id="jdoe".
>
>
> Unfortunately it does not work.
> Does anyone know if what I want is possible? If so, would you be so
> kind as to provide sample sql statement?
> Thanks!
>
>
> Robert C. Best III - [EMAIL PROTECTED]
> District Technology Coordinator
> for N.E.R.I.C. at Potsdam Central School
> Phone: (315) 265-2000 x266
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
