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

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.