CODEWITHSUNDEEP
mysqlsql
Martin AJ
Martin AJ

Reputation: 6707

How to protect an unsigned column against negative numbers?

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

Answers (2)

Sebastian Brosch
Sebastian Brosch

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

stack
stack

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

Related Questions