CODEWITHSUNDEEP
mysql
Kayode
Kayode

Reputation: 11

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, processed

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 = '[email protected]'

Upvotes: 1

Views: 109

Answers (2)

Jay
Jay

Reputation: 27464

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='[email protected]';

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.

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

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 = '[email protected]'

Upvotes: 0

Related Questions