MySQL MAX() function to compare numeric values in an update?
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.
Related videos on Youtube
DivideByHero
Updated on June 04, 2022Comments
-
DivideByHero almost 2 years
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!
-
Jonas Äppelgran almost 11 yearsCorresponding
MIN()
function is calledLEAST()