I am using the below query on the below data. However, when I do this I am getting the error "Data truncated for column 'strength' at row 1" for every column and row. I researched it a little, and as far as I can tell, most people are getting this error because they are trying to use text or char. I have never seen this warning before and I am getting the expected results, but with 4,700 warnings.
SET strength = (strength * .999),
agility = (agility * .999),
guard = (guard * .999),
labour = (labour * .999),
IQ = (IQ * .999)
WHERE gym_train_since_cron = 0
strength * .999 is resulting in a number with more than 4 decimal places. That goes for all of these calculations.
You may wonder what the difference between the two functions are; it's the rounding behavior. For
TRUNCATE it will round a number towards 0, whereas
ROUND, depending on the numeric data type (exact or approximate) which in your case is decimal (an exact type), the following occurs (taken from Rounding Behavior):
For exact-value numbers, ROUND() uses the “round half up” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.
To demonstrate, here's an example using the strength value for user 4898 from your sample data:
strength * .999 = 16331143.7521566 -- Over 4 decimal places, hence the warning ROUND(strength * .999, 4) = 16331143.7522 -- Rounds up TRUNCATE(strength * .999, 4) = 16331143.7521 -- Rounds down