Sin Cats Sin Cats - 5 months ago 14
MySQL Question

Adding money to all user's accounts

we have a social network that consists of several user types and we want to add $7.04 to all customer accounts to celebrate the USA holiday 4th of July.

I have used the following SQL command:

UPDATE `database`.`databasename` SET fCreditAmount = fCreditAmount + 7.04 WHERE eUserType = 'C'


However when I do this it seems to credit multiple times. So instead of just giving each customer $7.04 it credits some customers $1000s.

Anyone know why this is occurring?

Our 'balance' table structure is:

iBalanceId (autoincrement)
iUserId
eUserType
eBalType
fCreditAmount
fDebitAmount
dEntryDate
dValueDate
vDescritption
vBalTypeCode
iRefId
vRefTypeCode
iAddedUserId
eAddedUserType
vProcessDescription
eBalanceStatus

Answer

It's hard to tell without more information, but it looks like you're adding $7.04 to every transaction in a user's history. So each user is getting 7.04 * numberOfTransactions in credit.

You should be adding a new transaction (an INSERT) in the desired amount. That way, each account will get the credit exactly once.

I certainly hope you didn't do this on your production database!