MySQL implementation of multi-value update based on condition

MySQL multi-condition batch update core syntax:

UPDATE Table name
SET 
    Fields1 = CASE 
        WHEN condition1 THEN value1
        WHEN condition2 THEN value2
        ELSE Fields1 
    END,
    Fields2 = CASE 
        WHEN condition1 THEN value3
        WHEN condition2 THEN value4
        ELSE Fields2
    END
WHERE 整体过滤condition;

Sample Scenario: Updated Product Inventory and Price

 
UPDATE products
SET
    stock = CASE 
        WHEN category_id = 5 AND price > 100 THEN stock - 10
        WHEN create_date < '2024-01-01' THEN stock + 5
        ELSE stock
    END,
    price = CASE 
        WHEN category_id = 5 THEN price * 0.9
        WHEN stock > 1000 THEN price * 0.8
        ELSE price
    END
WHERE status = 'active';

Efficient writing skills:

Batch update logic

UPDATE users
SET 
    vip_level = CASE
        WHEN score >= 1000 THEN 'Diamond'
        WHEN score >= 500 THEN 'Gold'
        ELSE vip_level
    END,
    discount = CASE 
        WHEN score >= 1000 THEN 0.7
        WHEN score >= 500 THEN 0.8
        ELSE discount
    END
WHERE registration_year = 2024;

Key notes:

Conditional priority: WHEN clauses match in writing order
Must include ELSE: prevent accidental overwriting of data, it is recommended to keep the original value
Performance optimization: WHERE clause requires index fields
Transaction processing: Large batch updates are recommended to be executed in batches
Multi-table association: JOIN can be used to implement cross-table condition updates

UPDATE orders o
JOIN payments p ON  = p.order_id
SET 
     = CASE 
        WHEN  = 1 THEN 'completed'
        ELSE 'pending'
    END,
    p.processed_at = NOW()
WHERE o.create_date > '2024-06-01';

Extended solution: Use IF functions to simplify binary judgment

UPDATE employees
SET
    salary = IF(performance > 90, salary * 1.2, salary),
    bonus = IF(join_year < 2020, bonus + 5000, bonus)
WHERE department = 'Engineering';

It is recommended to add transaction control (BEGIN/COMMIT) and paging update (LIMIT) according to the actual data volume to avoid long-term locking of tables.

This is the end of this article about the implementation of MySQL multi-value update according to conditions. For more related content on MySQL multi-value update, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!