CODEWITHSUNDEEP
mysql
Xaver
Xaver

Reputation: 11682

Update field with max and min value in mysql

I would like to update a percentage field with a maximum value of 1 and a minimum of 0 with a relative value (eg. "add 25% to the current value")

This obviously doesn't work:

UPDATE table SET field = MAX(0, MIN(1, field+0.25))

Update:

If the value is eg 0.85 it should update to 1

Update

I'm using this now:

UPDATE table SET field = GREATEST(0, LEAST(1, field+0.25))

Upvotes: 4

Views: 1260

Answers (1)

juergen d
juergen d

Reputation: 204864

UPDATE your_table 
SET field = case when field > 0.75 
                 then 1
                 else field + 0.25
            end

Especially in MySQL you can do

UPDATE your_table 
SET field = least(1, field + 0.25)

For values that can also be negativ you can use

update your_table
SET field = case when field + ? > 1.0 then 1 
                 when field + ? < 0.0 then 0 
                 else field + ? 
            end

Upvotes: 4

Related Questions