How to update a MySQL column by subtracting a value with some conditions?

Updating a MySQL column by subtracting a value with conditions is a common database operation. You can use the UPDATE statement with a WHERE clause to modify specific rows based on criteria while performing arithmetic operations on column values.

Syntax

Following is the basic syntax for updating a column by subtracting a value with conditions −

UPDATE table_name 
SET column_name = column_name - value 
WHERE condition;

Where −

• table_name − The name of the table to update
• column_name − The column to modify
• value − The number to subtract from the current column value
• condition − The criteria that determines which rows to update

Example − Basic Subtraction with Condition

Let us first create a table to demonstrate the subtraction operation −

CREATE TABLE DemoTable (
    Score INT
);

Query OK, 0 rows affected (0.65 sec)

Insert some records in the table using insert command −

INSERT INTO DemoTable VALUES (45);
INSERT INTO DemoTable VALUES (29);
INSERT INTO DemoTable VALUES (56);
INSERT INTO DemoTable VALUES (24);
INSERT INTO DemoTable VALUES (32);

Query OK, 1 row affected (0.11 sec)
Query OK, 1 row affected (0.11 sec)
Query OK, 1 row affected (0.09 sec)
Query OK, 1 row affected (0.09 sec)
Query OK, 1 row affected (0.09 sec)

Display all records from the table using select statement −

SELECT * FROM DemoTable;

+-------+
| Score |
+-------+
|    45 |
|    29 |
|    56 |
|    24 |
|    32 |
+-------+
5 rows in set (0.00 sec)

Following is the query to update MySQL column by subtracting a value with some conditions −

UPDATE DemoTable 
SET Score = Score - 2 
WHERE Score > 30;

Query OK, 3 rows affected (0.14 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Let us check the table records once again −

SELECT * FROM DemoTable;

+-------+
| Score |
+-------+
|    43 |
|    29 |
|    54 |
|    24 |
|    30 |
+-------+
5 rows in set (0.00 sec)

As you can see, only the rows where Score > 30 (originally 45, 56, and 32) were updated by subtracting 2 from their values.

Example − Multiple Conditions

You can combine multiple conditions using logical operators. Let us create another example with more complex conditions −

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Stock INT,
    Price DECIMAL(10,2)
);

INSERT INTO Products VALUES 
(1, 'Laptop', 15, 800.00),
(2, 'Mouse', 50, 25.00),
(3, 'Keyboard', 30, 45.00),
(4, 'Monitor', 8, 200.00),
(5, 'Tablet', 12, 300.00);

Now, let us subtract 3 from stock for products that have more than 10 items in stock AND cost less than $100 −

UPDATE Products 
SET Stock = Stock - 3 
WHERE Stock > 10 AND Price < 100.00;

Query OK, 3 rows affected (0.08 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Check the updated results −

SELECT * FROM Products;

+-----------+-------------+-------+--------+
| ProductID | ProductName | Stock | Price  |
+-----------+-------------+-------+--------+
|         1 | Laptop      |    15 | 800.00 |
|         2 | Mouse       |    47 |  25.00 |
|         3 | Keyboard    |    27 |  45.00 |
|         4 | Monitor     |     8 | 200.00 |
|         5 | Tablet      |     9 | 300.00 |
+-----------+-------------+-------+--------+
5 rows in set (0.00 sec)

Only Mouse, Keyboard, and Tablet met both conditions (Stock > 10 AND Price

Example − Using IN and BETWEEN Conditions

You can also use other conditional operators like IN, BETWEEN, and LIKE for more specific updates −

-- Subtract 5 from stock for products with IDs 1, 3, or 5
UPDATE Products 
SET Stock = Stock - 5 
WHERE ProductID IN (1, 3, 5);

-- Subtract 10 from price for products with stock between 10 and 20
UPDATE Products 
SET Price = Price - 10.00 
WHERE Stock BETWEEN 10 AND 20;

Safety Considerations

When performing subtraction operations, consider these important points −

• Prevent negative values − Add conditions to ensure values don't become negative if that's not desired
• Use transactions − For critical operations, wrap updates in transactions for rollback capability
• Test first − Always test your UPDATE statement with a SELECT query first
• Backup data − Consider backing up important data before bulk updates

Example − Preventing Negative Values

-- Only subtract if the result won't be negative
UPDATE Products 
SET Stock = Stock - 5 
WHERE Stock > 5 AND ProductName LIKE '%Laptop%';

Example − Using CASE for Conditional Subtraction

UPDATE Products 
SET Price = CASE 
    WHEN Price > 100 THEN Price - 20 
    WHEN Price > 50 THEN Price - 10 
    ELSE Price - 5 
END
WHERE Stock > 0;

Conclusion

Updating MySQL columns by subtracting values with conditions is accomplished using the UPDATE statement combined with arithmetic operations and WHERE clauses. This technique allows you to selectively modify data based on specific criteria while performing calculations on existing column values. Always test your conditions carefully and consider data safety measures before executing bulk updates.