Reputation: 6707
I have a query like this:
UPDATE users SET reputation = reputation - 2 WHERE id = :id
It throws an error sometimes when the current reputation
is 0
. Since reputation
column is unsigned
. Is there any idea how can I solve the problem?
Upvotes: 1
Views: 83
Reputation: 43604
You can use CASE WHEN
to set the value if it is a positive value or a default value on negative value:
UPDATE users
SET reputation = (CASE WHEN (reputation - 2 >= 0) THEN reputation - 2 ELSE 0 END)
WHERE id = :id
You can set a default value on the ELSE
part, if the value is lower than 0 (in this case 0
).
As @stack already mentioned in his answer you can use GREATEST
(instead of CASE WHEN
):
UPDATE users
SET reputation = GREATEST(reputation - 2, 0)
WHERE id = :id
If you don't want to UPDATE
in such a case you can check the value on WHERE
:
UPDATE users
SET reputation = reputation - 2
WHERE id = :id
AND (reputation - 2) >= 0
Upvotes: 1
Reputation: 10228
Use GREATEST()
:
UPDATE users SET reputation = GREATEST(reputation - 2, 1) WHERE id = :id
And if reputation
is zerofill
, then GREATEST(reputation - 2, 0)
.
Upvotes: 1