Mysql: Update Field Only if Condition Is Met

sql

conditional-updates

case-statement

data-integrity

byNikita Barsukov·Aug 6, 2024

Update with condition in MySQL:

UPDATE table_name
SET column_name = 'new_value'
WHERE condition;

Set status to 'active' for users with points > 100:

UPDATE users
SET status = 'active'
WHERE points > 100;

This executes the update only if the WHERE clause is met.

The Art of Conditional Updates

Using 'CASE' Statement

The CASE statement allows us to test multiple conditions within a single UPDATE statement, providing granular control and saving us from inefficiencies.

UPDATE products
SET price = CASE
    WHEN quantity > 50 THEN price * 0.95 -- Discount time, Yay! 
    WHEN quantity <= 10 THEN price * 1.10 -- sorry folks, it's pricey now
    ELSE price -- chill, prices stay the same
END;

The query checks the quantity for each row and updates the price accordingly. It does nothing if none of the CASE conditions match.

Leveraging the 'IF' Function

For single-condition checks, the IF function is a tidier alternative.

UPDATE employees
SET salary = IF(performance_rating > 8, salary * 1.15, salary) -- if you worked hard, get some bonus!
WHERE employee_id = 123;

A quick way to give employee_id "123" a pay rise based on their performance rating.

Multitasking with Multiple Fields

Several fields can be updated simultaneously, some conditionally, others unconditionally. Here's a taste of it.

UPDATE orders
SET 
  quantity = quantity - 10, -- always deduct 10
  status = CASE
    WHEN item_stock < 20 THEN 'out of stock' -- Oops! time to restock
    ELSE 'available' -- you're in luck! products on the shelf
  END
WHERE order_id = 789;

Diving Deeper into Special Scenarios

Addressing 'Zero Changes'

No worries if the fields you're trying to change are already updated. MySQL's got your back. It doesn't log an update if the new value equals the existing one, relieving the database overhead.

Keeping Integrity with Atomicity

Ensure your CASE and IF conditions accurately cover all possible scenarios, maintaining data integrity. Any slight misjudgment may lead to update mishaps.

Knowing MySQL Behavior Inside Out

Familiarize yourself with MySQL's behavioral intricacies through their official documentation. It's like a treasure chest of knowledge nuggets for conditional expressions in UPDATE.

Practical Use Cases

Updating Field Values in an E-commerce Database

UPDATE products
SET price = CASE
    WHEN season_end = TRUE THEN price * 0.75 -- End-of-season sale! 🛒
    ELSE price
END
WHERE category = 'Seasonal';

This query is efficient for updating product prices based on sales or events.

UPDATE users
SET last_login = IF(last_login < NOW() - INTERVAL 1 YEAR, NOW(), last_login) -- Are you a time traveler from last year?
WHERE username = 'johndoe';