MySQL MAX() function to compare numeric values in an update?
mysql, sql
Solution
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.
Problem
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!