UPDATE table SET field=field-10; (field is unsigned INT and less than 0).

UPDATE table SET field=field-10;
field is INT and less than 10

Why does it explode to max int value (4 trillion something)? How are things like this normally handled? Do I have to use multiple queries or php?

What's the easiest way to make it not explode and go to 0 if the value is negative?

 

simplest way to do this is, first you can get field data in one query than do field-10 through php if comes negative set field = 0 using query.


Sheetal G

 

I was hoping it was a bug of some kind or something like that... Thanks.

 

Maybe using 'CASE' or 'IF', so something online these lines:

UPDATE table SET field = CASE WHEN field < 10 THEN 0 ELSE field-10 END

 

Thanks mfscripts, that's excellent!

 

An unsigned number is a number without a sigh - IOW there are no negative numbers in the set of unsigned numbers. If you want a negative number saved, you have to change the type of the field to signed integer. (Or do the math yourself: -1 is max integer. -2 is max integer - 1. Etc.)