MAX() in UPDATE query with field does not work

Question

I try to update multiple rows at the same time but amount can have different starting values. I want to subtract x amount from amount without going below 0. I used UNSIGNED for the amount field in order to protect the number never to fall below 0.

The following query refuses to update all rows that fall below 0 because of the UNSIGNED state:

UPDATE `table`
   SET `amount` = `amount` - 35
 WHERE `id`
    IN (26984, 131881, 985550, 985569, 985586, 1086766, 1189724)

In case amount is for example 12, if you subtract 35, it falls below 0 and refuses to update. But I need it to update to 0.

Then I tried to think of MAX() to help me not fall below 0, but this does not work:

UPDATE `table`
   SET `amount` = MAX(`amount` - 35, 0)
 WHERE `id`
    IN (26984, 131881, 985550, 985569, 985586, 1086766, 1189724)

Any idea how to make this work and to make sure the value never falls below 0?

Answer 1

Thanks, this is how it worked in the end:

UPDATE `table`
   SET `amount` = GREATEST(`amount` - 35, 0)
 WHERE `id`
    IN (26984, 131881, 985550, 985569, 985586, 1086766, 1189724)

And discarding UNSIGNED from amount