CODEWITHSUNDEEP
mysqlmax
user1022585
user1022585

Reputation: 13651

UPDATE mysql max value

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?

Upvotes: 3

Views: 227

Answers (2)

T I
T I

Reputation: 9943

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

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

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

or:

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

Upvotes: 4

Related Questions