MySQL: How to decrement an unsigned int column?

Question

I have a table with a column unsigned int, let's name it col1.

I want to decrement the column by a number, if value after decrement is less than 1 then set it to 1. Like this:

UPDATE `tbl1` SET `col1` = GREATEST(1, `col1`-3);

This works fine, but if col1 is 0 or 1 or 2 and I try to do -3 then MySQL throws an error.

I've tried this and this but didn't worked.

Answer 1

You can use a case clause

update tbl1 set col1 =  (case when col1 <= 3 then 1 else (col1 - 3) end);

As @RDFozz described :

With the OP's listed code, col1 - 3 must be computed, and since it's an unsigned integer, the result must be an unsigned integer. This code prevents the computation from happening unless the result would actually be an unsigned integer.

UPDATE :

Another possible way as suggested by @kondybas

update tbl1 set col1 = IF(col1<=3, 1, col1-3);

Answer 2

if value after decrement is less than 1 then set it to 1.

Just add a WHERE statement

UPDATE tbl1
  SET col1 = col1 - 1
WHERE col1 >= 2;

Answer 3

Yet another way to skin this cat:

UPDATE tbl1 SET col1 = GREATEST(4, col1) - 3

Possibly more efficient:

UPDATE tbl1 SET col1 = GREATEST(4, col1) - 3
    WHERE col1 > 1;

Answer 4

The minimum value that an unsigned int can have is 0, you cannot set it to negative unless it is signed. You need to implement logic to handle the values in the column so your GREATEST(1, 'col1'-3) isn't violating the rule. CASE WHEN in that statement may work as well as pulling out a filtered subset of the data with a subquery.

MySQL Reference

Stack Overflow Reference

You can also cast your unsigned int as a signed int and then perform the arithmetic.

Stack overflow reference on casting to signed