MYSQL Update with maximum value

[BuildMyWeb]

Im having a hard time even explaining this so a search was not fruitful. 

 

I have an INT column in a mysql table.  i want to UPDATE it in increments of 10 but the value should never exceed 100.  what is an efficient way to do this?

 

ie.  if the current value in the field is 96, when it tried to update

field = field+10

, it should dynamically only be

field = field+4

so that we do not exceed the 100 mark.

[CroNiX]

Using a MySQL IF() statement in your query.

SELECT IF(field + x >= 100, 100, field + x) AS total

Edited December 3, 2014 by CroNiX

[BuildMyWeb]

thanks Cronix.  for anyone who might find this useful, here is my query syntax:

$query_ap     = 'UPDATE ' . C_T_USERS . ' SET ap = IF(ap+8 >= 100, 100, ap+ WHERE status = "active" AND ap < 101';

[CroNiX]

Sure, but I don't get why you'd be incrementing by 8 or any other number if you said you were incrementing by 10?

[Barand]

You could use SQL's LEAST() function also

 

... SET ap = ap + LEAST(100-ap, 10) ...

 

so if ap is 93 then only 7 is added but if ap is <91 then 10 is added.