update query problem

i have three tables on mysql database which are:

RECHARGE with these columns: rid, uid,res_id, agent_id, batch_id, rcard_order, serialno, email, units, bankid, paydate, slipno, rpin, amtpd, bonus, description, crSender, crSenderId, transaction_ref,rechargeDate, pr开发者_如何学编程ocessed

SENT with these columns: sendid, uid, res_id, recipients, volume, ffdaily, message, sender, msgtype, flash, mob_field, wapurl, date

BILL with these columns: bid, uid, email, unitBals, lastusedate

The question is these:i want a query that will subtract the sum of volume in SENT table from units in RECHARGE table and use the result to update the unitBals column on BILL table where the primary key joining the three tables is their uid.

i used this query but it is not giving me the same answer as when i sum(volume) and subtract it from sum(units) separately doing the calculation on my own

update bill set unitbals = (SELECT sum( recharge.units ) - sum( sent.volume )
                            FROM sent, recharge
                            WHERE sent.uid = recharge.uid)
where email = 'info@dunmininu.com'

There are two problems here. First, from the fact that you are using sum, I take it that there can be more than one Recharge record for a given Uid and more than one Sent record for a given Uid. If this is true, then when you do the join, you are not getting all the Recharges plus all the Sents, you are getting every combination of a Recharge and a Sent.

For example, suppose for a given Uid you have the following records:

Recharge:
Uid  Units
42   2
42   3
42   4

Sent
Uid  Volume
42   1
42   6

Then a query

select recharge.units, sent.volume
from recharge, sent
where recharge.uid=sent.uid

will give

Units  Volume
2      1
2      6
3      1
3      6
4      1
4      6

So doing sum(units)-sum(volume) will give 18-21 = -3.

Also, you're doing nothing to connect the Uid of the Sent and Recharge to the Uid of the Bill. Thus, for any given Bill, you're processing records for ALL uids. The Uid of the Bill is never considered.

I think what you want is something more like:

update bill
set unitbals = (SELECT sum( recharge.units ) from recharge where recharge.uid=bill.uid)
  - (select sum(sent.volume) from sent where sent.uid=bill.uid)
where email='info@dunmininu.com';

That is, take the sum of all the recharges for this uid, minus the sum of all the sents.

Note that this replaces the old value of Unitbals. It's also possible that you meant to say "unitbals=unitbals +" etc.

I think you need separate sum in the two tables:

update bill
set unitbals =
    ( SELECT sum( recharge.units )
      FROM recharge
      WHERE bill.id = recharge.uid
    ) -
    ( SELECT sum( sent.volume )
      FROM sent
      WHERE bill.id = sent.id 
    ) 
where email = 'info@dunmininu.com'