Decrement in mysql goes past zero

Question

I am trying to do this in mysql:

UPDATE table SET value = value - 1 WHERE blah blah

If value is 0 and this is run value is set to 4294967295. This is because it is an unsigned integer so it is looping round back to the maximum value.

How would I go about making it stay on zero instead? Can I do this purely in the sql?

Answer 1

`AND value > 0`

Answer 2

You can run a second query or if-statement, checking whether it is set to the maximum and set it back to 0. But this is some kind of stupid...

Write a quick function which checks the entry, if it is already 0 do not perform any update query.

(if value >= 0, do not perform xy)

Answer 3

SET value = IF(value=0,0,value-1)

Answer 4

Use the where clause to say value > 0

Sunday night SQL always hurts me too. :-)

Answer 5

Just append to the SQL string AND value > 0