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

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

Kanban vs Scrum: The two frameworks of agile principlesMiscbin2hex() and chr() String Functions in PHPPHPWhat is the difference between float and double?MiscHow to hide a DIV on clicks outside of it using jQuery?jQuerySimple Page Hit Counter in PHPPHPWhat’s a page re-rendering in React?ReactAlternate Style Sheets in CSSCSSWhat Is a Graph Database?MiscHow to define constants in PHP?PHPQuick Introduction to 11 Array Iteration Methods in JavaScriptJavaScriptFETCH clause in PostgreSQLPostgresSELF JOIN in PostgresPostgres