YogeshChauhan . com

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;
dreamhost

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 uninstall Cocoapods from the Mac OS? #4 How to add Read More Read Less Button using JavaScript? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

May 7 How to disable right click and drag and drop of images using jQuery? May 7 How to render Lists in React? May 7 What’s the difference between variables in CSS and SCSS (Sass)? May 7 How to define variables in SCSS (Sass)? May 7 How to show and hide an element on click in React? May 5 Use inline if to make a shorter conditional syntax in React

You might also like these

Kubernetes vs. Docker? It’s a misleading phraseMiscHow to Pop an Alert Box in PHP?JavaScriptHow to avoid element shift on border change while hovering in CSS?CSSArguments in @mixin rules in SCSS (Sass)SCSSHow to apply style to the first-child using jQuery?jQuerySolution for the error Commit failed – exit code 1 received in Desktop GithubMiscA complete diagram with building blocks of an Angular applicationAngularHow to Use SQL MAX() Function with Dates?SQL/MySQLContent Blocks in SCSS (Sass)SCSSHow to change HTML content on click using JavaScript?JavaScriptWHERE Clause in PostgresPostgresWhat does it mean by Continuous Integration, Continuous Delivery and Continuous Deployment?Misc