Decrement value in mysql but not negative

Question

I want to decrement a value when user delete it in php and mysql. I want to check not to go below than 0. If value is 0 then do not decrement.

mysql_query("UPDATE table SET field = field - 1 WHERE id = $number");

If field is 0 then do not do anything

juergen d · Accepted Answer · 2023-02-20T07:23:15.823

94

Add another condition to update only if the field is greater 0

UPDATE your_table 
SET field = field - 1
WHERE id = $number
AND field > 0

edited Feb 20 '23 at 07:23

answered Apr 29 '13 at 11:20

juergen d

201,996
37
293
362

3

Thanks, I feel pretty stupid now but I'm happy to have found your answer. – Pavin Joseph Sep 15 '19 at 12:32

score 46 · Answer 2 · answered Apr 29 '13 at 11:22

46

You could prevent the new value to drop below zero by using GREATEST(). If the value drops below zero, zero will always be greater than your calculated value, thus preventing any value below zero to be used.

UPDATE  table
SET     field = GREATEST(0, field - 1)
WHERE   id = $number

And on a side note: Please don't use mysql_* functions any more. They are deprecated and will eventually be removed from PHP. Use PDO or MySQLi instead.

answered Apr 29 '13 at 11:22

Till Helge

9,253
2
40
56

Only if you use [`mysqli_connect()`](http://php.net/mysqli_connect) as well. You need to replace everything that starts with `mysql_` with another function either from MySQLi or PDO. – Till Helge Apr 29 '13 at 11:25
best. this works with increment, not only decrement. – Blackfire Nov 10 '16 at 19:07
Nice solution! It works not only decrement, but with any number that is subtracted. On the contrary, accepted answer is OK only for decrement/increment. – Boolean_Type Nov 21 '16 at 14:14
2

In this case I got error "Error Code: 1690. BIGINT UNSIGNED value is out of range in `table`.`field` - 1" if field is unsigned. – frops Feb 21 '18 at 10:03
2

I had the same error as @frops. The solution is to add a CAST: GREATEST(0, CAST(field AS SIGNED) - 1) – Matthieu.V Aug 27 '18 at 09:18
Thanks a lot for answer and dear @Matthieu.V : ) – نرم افزار حضور و غیاب Apr 16 '19 at 13:39

score 12 · Answer 3 · answered Feb 07 '15 at 15:53

12

The option using GREATEST will not work in newer MySQL versions, and the accepted answer can be unuseful if you want to update multiple fields instead of one. My solution for this problem is using IF:

UPDATE  table
SET     field = IF(field > 0, field - 1, 0)
WHERE   id = $number

answered Feb 07 '15 at 15:53

Eelco Luurtsema

1,149
2
9
12

2

I don't see anything that says the GREATEST function won't work with newer MySQL versions. As of October 2016, MySQL version 2.7 docs still have it listed, with no mention of deprecation: http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_greatest – Jordan Lev Oct 15 '16 at 03:47
[`GREATEST()`](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_greatest) still available in MySQL 8.0 – jchook Jun 23 '20 at 01:52

score 3 · Answer 4 · edited Oct 28 '17 at 18:13

3

UPDATE table SET field = case when (field - 1) >0 then (field - 1)
else field end
WHERE id = $number

edited Oct 28 '17 at 18:13

Arun Vinoth-Precog Tech - MVP

22,364
14
59
168

answered Oct 28 '17 at 16:24

Niket

31
3

score 1 · Answer 5 · edited Oct 09 '15 at 12:31

1

 UPDATE `table_name` SET field = field-1 WHERE `id` = '".$id."' AND field > 0

Note: field's data-type should be an INTEGER.

edited Oct 09 '15 at 12:31

Criesto

1,985
4
32
41

answered Oct 09 '15 at 11:45

Aatif

21
2

score 1 · Answer 6 · answered Oct 08 '19 at 13:39

1

if the field is int unsigned, below is the best:

UPDATE table 
SET field = field - 1
WHERE id = $number
and field > 0

# or
UPDATE  table
SET     field = IF(field > 0, field - 1, 0)
WHERE   id = $number

answered Oct 08 '19 at 13:39

Dev

36
3

SNS - Web et Informatique · Answer 7 · 2022-02-04T06:45:41.613

For someone who search an exemple with PDO Quickly ...

<?php

/**
 * Class Set_quantity_product
 */
 class Set_quantity_product {
    
    
  /**
    * Set_quantity_product::update_quant_prod( $id, $number, $to_do );
    *
    * @param  {int} $id        a product id
    * @param  {int} $number    a number to increment or decrement for a value in DB
    * @param  {str} $to_do     'incr/decr'
    * @return {void}           increment or decrement but always return 0 if quant < 0
    */
    public static function update_quant_prod( $id, $number, $to_do ){
    
       $DATA_BASE = new PDO('mysql:host='.$YOUR_HOST.';dbname='.$YOUR_DB_NAME.';charset=utf8', $YOUR_USER_NAME, $YOUR_DB_PASSWORD, array( PDO::ATTR_PERSISTENT => false));
    
       // Note:
       // UPDATE  products
       // SET     quant = GREATEST( 0, quant+:ope )
       // WHERE   id = :id
       // increm or decrement but do nothing if quant is > quant in DB
    
       // pass quant number to affect to negative for decrement
       $number = ( $to_do == 'decr' ) ? $number*-1 : $number;
    
       // UPDATE ONE PROD. QUANT. 'ope' -> calcul operation
       $ARR_pdo = array( 'id' => (int) $id,
                            'ope' => $number );
       $sql = 'UPDATE products SET
          quant = IF(quant+:ope >= 0, quant+:ope, 0) WHERE id=:id';
    
       // prepa. SQL
       $request = $DATA_BASE->prepare($sql);
    
       // exec. request
       $request->execute($ARR_pdo);
    
       // PDO closeCursor
       $request->closeCursor();
    
       // return true for test
       return true;
   }
   /**
    * Set_quantity_product::update_quant_prod( $id, $number, $to_do );
    */
    
 }
 /**
  * Class Set_quantity_product
  */

?>

Use : ( consider you have a product with an id=42 )

Set_quantity_product::update_quant_prod( 42, 5, 'decr' );

if your quant in DB is 6 -> this set the value at 1

if your quant in DB is 5 -> this set the value at 0

if your quant in DB is 4 -> this set the value at 0

Decrement value in mysql but not negative

7 Answers7

Linked