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;