Why does the balance become 1.00 whenever the sql command below is executed no matter what value of $cost is?

Question

Why does the balance become 1.00 whenever the sql command below is executed no matter what value of $cost is?

update account set balance=balance-'$cost' and username='steven'"

The value of balance is bigger than $cost initially, after the execution, the balance of 'steven' becomes 1.00. What's wrong?

Say, when the initial balance is 2000.00, the $cost is 300, after this subtraction, the balance becomes 1.00. What's wrong?

I am using MySQL.

Answer 1

The query you are looking for that does what you are trying to do (but is probably still wrong) is:

update account set balance=balance-'$cost', username='steven'

But probably, you want the following? Or are you really trying to set the balance and username for every row in the table for some reason?

UPDATE account SET balance = balance - $cost WHERE username = 'steven'

Answer 2

You've forgotten to put in WHERE clause:

UPDATE account SET balance=balance-'$cost' WHERE username='steven' LIMIT 1;

What is happening in your query

UPDATE account SET balance=balance-'$cost' AND username='steven';

According to MySQL Operator Precedence, the substraction takes place first, which, for the example you gave, will return a positive number:

UPDATE account SET balance=(balance-'$cost') AND username='steven';
UPDATE account SET balance=(1700) AND username='steven';

Then, the assignment (=) of username to 'steven' takes place, which returns 'steven':

UPDATE account SET balance=(1700) AND (username='steven');
UPDATE account SET balance=(1700) AND ('steven');

Then, the (AND) boolean operator kicks in, converts both the positive number and the string to boolean values, both TRUE, and then ANDs them, which will return TRUE:

UPDATE account SET balance=((1700) AND ('steven'));
UPDATE account SET balance=(TRUE AND TRUE);
UPDATE account SET balance=(TRUE);

Finally, since balance is a floating point number, TRUE gets converted into a number, which defaults to to 1.0:

UPDATE account SET balance=(TRUE);
UPDATE account SET balance=1;

This will affect all records, not just the one for username 'steve'.

Answer 3

Looking for the obvious...

I find it odd that you'd change the username at the same time you update the balance. Could it be that you meant "... where username = 'steven'"?