How Can I Efficiently Update Multiple Rows in MySQL?

Optimizing MySQL Multi-Row Updates

While single-row MySQL updates are straightforward, updating numerous rows efficiently requires a different approach. Batch updates offer substantial performance improvements over individual UPDATE statements, minimizing database interactions.

Leveraging INSERT ... ON DUPLICATE KEY UPDATE

MySQL's INSERT ... ON DUPLICATE KEY UPDATE construct is ideal for this task. This single statement handles both insertion and updating, based on whether a matching key already exists.

Practical Multi-Row Update Example

Let's illustrate with a sample table:

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

The following query efficiently updates multiple rows:

<code class="language-sql">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);</code>

This query's functionality:

• Attempts to insert rows with IDs 1, 2, 3, and 4.
• If a row with a given ID already exists, it updates that row's Col1 and Col2 values to the corresponding values provided in the VALUES clause.
• If a row with a given ID does not exist, it inserts a new row with the specified data.

This consolidated approach dramatically improves performance by reducing the number of database server round trips, leading to faster update operations.

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