CODEWITHSUNDEEP
sqlmax
user1022585
user1022585

Reputation: 13651

SQL UPDATE add to field with MIN/MAX

I have this statement

UPDATE table SET health = health + 1 WHERE name='Tom'

How can I set a MAX for the number field in the statement based off? Pseudo code:

UPDATE table set health = min(health+1, max_health) WHERE name='Tom'

Upvotes: 0

Views: 50

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

We can use CASE also

UPDATE table 
SET health = (CASE WHEN health+1 < max_health THEN 
                  health+1 
              ELSE 
                  max_health 
              END) WHERE name='Tom'

AND IIF --SQL Server

UPDATE table SET health = IIF (health+1 < max_health, health+1, max_health) WHERE name='Tom'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270553

The simplest way is to modify the where:

UPDATE table
    SET health = health + 1
    WHERE name = 'Tom' AND health < max_health;

Some databases support the least() function:

UPDATE table
    SET health = LEAST(health + 1, max_health)
    WHERE name = 'Tom' ;

You can also do this using CASE.

However, I think the first method is the simplest.

Upvotes: 1

Related Questions