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 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to add recaptcha version 3 to PHP website?PHPHow to change the Login Logo in WordPress?WordPressHow to add today’s date in HTML input date value using JavaScript?JavaScriptSome SQL LIKE Operators We Need to Keep in MindSQL/MySQLHow to check if checkbox is checked or not using JavaScript?JavaScriptClearwater Seafoods – B2C in ChinaMiscellaneousConditional operator in JavaScript (aka ternary operator)JavaScriptURL paths in DrupalDrupalHow Do You Make a Private VPN?MiscellaneousCSS Overflow Property with ExamplesCSSWhat is React? Learn the basicsReactHow to get the full URL of current page in PHP?PHP