MySQL: How to decrement an unsigned int column?

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);

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;

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;

Tags:

Mysql