How to Update Field to Subtract Value from Existing Field Value in MySQL but Not Negative

The UPDATE and SET commands help to update existing field values in MySQL. You can use these commands to add/subtract value to/from existing field value in a single query in MySQL. When you run the query to subtract value from an existing field value, the updated value may decrement to a negative value.

In the example SQL query, we will show you how to decrement value from an existing field in MySQL database, but not negative value. If the existing value is 0, decrement does not execute.

Use the GREATEST() method with the UPDATE and SET command to subtract the value from an existing field in MySQL but not a negative value.

UPDATE  table_name
SET     field_name = GREATEST(0, field_name - $subtract_value)
WHERE   id = $row_id

Leave a reply

keyboard_double_arrow_up