UPDATE mysql max value

Question

I have a field in my database that I don't want to exceed 100. Is there something in a query I can add to make sure it doesn't?

I usually end up doing this:

UPDATE table SET field = field + $x UPDATE table SET field = 100 WHERE field > 100

Does anything like this exist?

ypercubeᵀᴹ · Accepted Answer · 2012-07-30 22:28:16Z

UPDATE table SET field = CASE WHEN field + $x <= 100 THEN field + $x ELSE 100 END ;

or:

UPDATE table SET field = LEAST(field + $x, 100) ;

T I · Accepted Answer · 2012-07-30 22:36:48Z

what about a trigger? something like

CREATE TRIGGER tg_limit BEFORE UPDATE ON tbl FOR EACH ROW BEGIN IF NEW.fld > 100 THEN SET NEW.fld = 100; END IF; END

2 Answers 2