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 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #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
How to change style of all direct children of an element using jQuery?jQueryHow to get query string values in JavaScript? (URL Manipulation using URLSearchParams)JavaScriptUseful (and probably ignored) HTML tags: Part 2HTMLHow to get category name using post id in WordPress?WordPressWordPress: How to print ACF repeater field values?WordPressALTER DATABASE in PostgreSQLPostgresUse inline if to make a shorter conditional syntax in ReactReactWhat’s new in WordPress 5.5?WordPress5 Key Principles Of Good Website UsabilityUI/UXCSS Overflow Property with ExamplesCSSHow states work in React?ReactHow to catch .keypress() on body using jQuery?jQuery