MySQL MAX() function to compare numeric values in an update?

Question

When updating a row, I want to have a built-in check to do some bounds checking. Most languages have a MAX() function to return the maximum of the arguments passed, but MySQL seems to use MAX() for something else. For example:

UPDATE person SET dollars = MAX(0, dollars-20) WHERE id=1

I want to subtract 20 dollars from person id 1, but I don't want dollars to ever be represented by a negative value, so I want a built-in comparison with 0. Does this work? Or is there another way? Thanks!

Answer 1

MAX is meant to find the maximum value in a column for the selected rows, not the maximum of a list of user-supplied values - try using the CASE statement:

UPDATE person SET dollars = CASE WHEN dollars - 20 < 0 THEN 0 ELSE dollars - 20 END WHERE id=1

Answer 2

There are two main methods here. CASE, and IF.

select CASE job_lvl
WHEN 1 THEN 'level 1
WHEN 2 THEN 'level 2'
ELSE 'Unknown level' end
from employee where job_id>0 ;

or

SELECT USERNAME, EMAIL, ID_NUMBER, DATE_REGISTERED, LAST_LOGIN, 
IF(ACTIVE=1,'Yes','No') ACTIVE, 
IF(ADMIN=1,'Yes','No') ADMIN 
FROM MEMBERS

Answer 3

MySQL supports a function called GREATEST(). It returns the largest value among a list of its arguments.

UPDATE person SET dollars = GREATEST(0, dollars-20) WHERE id=1

This isn't a standard function in ANSI SQL, so don't count on it being available in other brands of SQL database. If you need a vendor-independent solution, use the CASE syntax suggested by others. But if all you need to use is MySQL, this function is more concise.