Yogesh Chauhan's Blog

Query to increment or decrement value in MySQL ignoring negative values

in SQL/MySQL on April 26, 2020

Solution:


UPDATE table_name
SET value = value - 1
WHERE value > 0;

You can add more conditions in the WHERE clause if you want.

If you want to prevent that new value from turning negative (less then 0 if you keep decreasing), you could use GREATEST function. If the value drops below zero, zero will always be greater than your calculated value, thus preventing any value below zero to be used.

GREATEST() function MySQL

Syntax


GREATEST(value1,value2,...)

With two or more arguments, it returns the largest (maximum-valued) argument.

Example


mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0

GREATEST() returns NULL if any argument is NULL.

Main example with GREATEST()


UPDATE  table_name
SET value = GREATEST(0, value - 1)
WHERE   id = 1;

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to add Read More Read Less Button using JavaScript? #3 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #4 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
Use eq() method in jQueryjQuery4 ways to create Date Objects in JavaScriptJavaScriptUnderstand Inheritance in PHP in this Basic Example (For Beginners)PHPControl rendering using CSS content-visibility propertyCSSHow to create a multisite network in WordPress?WordPressWhat’s Interpolation in SCSS (Sass)?SCSSHow to add a scroll back to top button using JavaScript and CSS?CSSA complete guide to add responsive YouTube videos using HTML and CSSCSSThe 8 Golden Rules of Programming I Have Learned in My CareerMiscellaneousHow to list all PHP variables to debug the script?PHPThe difference between isFinite() method and isFinite() function in JavaScriptJavaScriptWhy does MOV matter in IT or any kind of projects?Miscellaneous