CODEWITHSUNDEEP
phpmysqlsql
user1111929
user1111929

Reputation: 6099

mysql reduce counter if positive

I'm trying to implement a simple credit system, so I have a column "credits" in my user table, and I'd like to do the following with PHP/MySQL:

if (credits >= 1)  
  credits = credits - 1;
else 
  error(not_enough_credits);

Is it possible to do this in one query? Obviously, I could do a SELECT query on the credits, and if that is >=1 do an UPDATE to reduce the counter by one, but that could give concurrency issues.

Upvotes: 0

Views: 100

Answers (2)

Hanky Panky
Hanky Panky

Reputation: 46900

UPDATE myTable SET credits = (credits - 1)  WHERE credits > 0

That where will work like an if in this case. This will also work fine for multiple users separately, given you provide it a valid user id.

UPDATE myTable SET credits = (credits - 1)  WHERE id=12345 AND credits > 0

Upvotes: 3

Aziz Shaikh
Aziz Shaikh

Reputation: 16534

You may use the CASE statement, like this:

UPDATE table1
SET credits = CASE WHEN credits >= 1 THEN credits-1 END

Upvotes: 1

Related Questions