Nuno_147 Nuno_147 - 15 days ago 4
SQL Question

Limit a value in SQL

Not that familiar with SQL.

I have the follow SQL :

UPDATE my_Table
SET num = num + 1
WHERE id = 1


I would like limit that
num
column that it won't exceed a threshold, assuming 100. If
num
will reach 100, I would like it to stay 100 and will not increase.

I assume if statement should be included here somehow. but cant figure out how.

Answer

You can use a CASE expression:

UPDATE my_Table 
SET num = CASE WHEN num+1 >= 100 THEN 100 ELSE num+1 END
WHERE id=1