Multiple Updates in MySQL

sql

batch-updates

mysql-performance

update-methods

byAlex Kataev·Aug 27, 2024

To execute multiple updates in one go using MySQL, you can use a CASE statement. Here's how:

UPDATE your_table
SET your_column = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ELSE your_column
END
WHERE your_column IN (value1, value2);

While this method works well for simple updates, let's dive into other more efficient and flexible options for complex databases.

Batch updates with INSERT ... ON DUPLICATE KEY UPDATE

To speed up your updates in MySQL, lean on the INSERT ... ON DUPLICATE KEY UPDATE method. See the following syntax:

INSERT INTO your_table (id, Col1, Col2)
VALUES 
    (1, 'Value1', 'Value2'),    -- Welcome, rookie data!
    (2, 'Value3', 'Value4'),    -- More newbie data? Let's roll.
    (3, 'Value5', 'Value6')     -- The newcomer squad is here!
ON DUPLICATE KEY UPDATE
    Col1 = VALUES(Col1),    -- Found an old pal? Let's renew your data!
    Col2 = VALUES(Col2);    -- Surprise, surprise! You're getting updated too.

Remember: maintaining a unique index on the id field helps the database decide whether to insert new data or update existing ones. This technique packs multiple operations into one effective query, which leads to less strain on your database server.

CASE vs INSERT ... ON DUPLICATE KEY UPDATE: the Decisive Duel

Choosing between CASE and INSERT ... ON DUPLICATE KEY UPDATE is highly context-specific.

Opt for the CASE statement when handling a few updates with simple conditions and you need the power of conditional logic.
The INSERT method shines when dealing with massive data where bulk updates are necessary, and conditions are less complex or uniformly distributed across rows.

According to performance tests, the CASE statement can be twice as fast as INSERT...ON DUPLICATE. However, don't jump to conclusions. Always benchmark in your own environment!

Tackling updates with JOINs and subqueries

For intricate updates, using JOINs with subqueries can bring in results from another table or dataset:

UPDATE your_table t
JOIN (
    SELECT id, new_value
    FROM another_table
) sub
ON t.id = sub.id
SET t.your_column = sub.new_value;

This method is like the Swiss Army knife of update methods. It allows you to join multiple tables, aligning updates with complex business rules.

Managing edge cases and complexity

Dealing with Non-Null Fields

Remember to explicitly set values in update statements when dealing with non-null fields without default values. This helps to keep unexpected "column 'Col' cannot be null" errors at bay:

INSERT INTO your_table (id, Col1, Col2)
VALUES (1, 'Value1', 'Value2')
ON DUPLICATE KEY UPDATE
    Col1 = IFNULL(VALUES(Col1), 'default_value'),    -- Don't leave me hanging, I'm not null!
    Col2 = IFNULL(VALUES(Col2), 'default_value');    -- I need a value too, or I'll throw a tantrum!

Considerations for Replication and Logging

Increased replication and log storage can be an outcome of updates. Transactional updates can produce more logs. Meanwhile, INSERT ... ON DUPLICATE KEY UPDATE tends to be leaner on logs. For replication-heavy databases, choose the INSERT method to avoid excessive binlogs and consequently, minimize replication lag.

AUTO_INCREMENT in InnoDB Gotcha

Remember, INSERT ... ON DUPLICATE KEY UPDATE may unwittingly increase AUTO_INCREMENT values in InnoDB, while my buddy MyISAM sits unaffected, munching on popcorn.

Clear Table Aliases in Updates

Maintain code readability by aliasing tables during UPDATE statements with multiple tables or subqueries:

UPDATE your_table AS t
INNER JOIN another_table AS a    -- Alias 'a' for readability, not for apple!
ON t.id = a.id
SET t.col = a.val;                -- Update like a boss!