How Can I Update Multiple MySQL Rows Simultaneously?

MySQL: Efficiently Updating Multiple Rows

Question: Is simultaneous updating of multiple MySQL rows possible?

Answer: Absolutely! The INSERT ... ON DUPLICATE KEY UPDATE statement provides an efficient solution.

Illustrative Example:

Let's say we have a table structured as follows, and we need to apply several updates:

Name	id	Col1	Col2
Row1	1	6	1
Row2	2	2	3
Row3	3	9	5
Row4	4	16	8

Required Updates:

• UPDATE table SET Col1 = 1 WHERE id = 1;
• UPDATE table SET Col1 = 2 WHERE id = 2;
• UPDATE table SET Col2 = 3 WHERE id = 3;
• UPDATE table SET Col1 = 10 WHERE id = 4;
• UPDATE table SET Col2 = 12 WHERE id = 4;

Consolidated Update Using INSERT ... ON DUPLICATE KEY UPDATE:

Instead of multiple individual UPDATE statements, we can achieve the same result with a single, more efficient query:

INSERT INTO table (id, Col1, Col2) VALUES (1, 1, 1), (2, 2, 3), (3, 9, 3), (4, 10, 12)
ON DUPLICATE KEY UPDATE Col1 = VALUES(Col1), Col2 = VALUES(Col2);

This query attempts to insert new rows. If a row with the specified id already exists (due to the primary key or a unique index on id), the ON DUPLICATE KEY UPDATE clause takes effect, updating the Col1 and Col2 values based on the values provided in the VALUES() clause. This method significantly improves performance compared to executing multiple individual UPDATE statements.

The above is the detailed content of How Can I Update Multiple MySQL Rows Simultaneously?. For more information, please follow other related articles on the PHP Chinese website!