How Can I Perform Multiple Record Updates in a Single MySQL Query?

Efficiently Updating Multiple MySQL Records

While MySQL's multi-row INSERT functionality is widely known, its capability to update multiple records simultaneously is often overlooked. This technique significantly improves efficiency compared to executing individual UPDATE statements.

Consider this example table:

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

Instead of multiple separate UPDATE statements like these:

<code>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;</code>

You can achieve the same result with a single, more efficient query:

<code>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 leverages the ON DUPLICATE KEY UPDATE clause. If a row with the specified id already exists, the corresponding Col1 and Col2 values are updated. If the row doesn't exist, a new row is inserted. This approach is considerably faster and more resource-friendly than executing multiple individual UPDATE queries.

The above is the detailed content of How Can I Perform Multiple Record Updates in a Single MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!