Mysql Update Set Max Values-CodePudding

I would like to update mysql. Here is the query

UPDATE citizen_data SET energy=energy ?,hunger=hunger ?,thirsty=thirsty ?,mood=mood ? WHERE username=?

The problem is

Energy should not more than 100, for example if we have 90 energy then we add 20. It would be 110. The energy should not exceed 100 for sure. So, how do I set it into 100?

CodePudding user response：

You may use the LEAST() scalar function here:

UPDATE citizen_data
SET energy = LEAST(energy   20, 100)
-- other set criteria here...

Here we take the smaller of energy 20 or 100. For energy values such that adding 20 would result in a value over 100, we update with 100, otherwise we update with that sum.