Can MySQL Update Multiple Rows in a Single Query?

MySQL batch row update

MySQL's INSERT statement allows multiple rows of data to be inserted simultaneously. But, can I update multiple rows with a single query?

Batch update query

Yes, you can use the INSERT ... ON DUPLICATE KEY UPDATE statement to update multiple rows at once. When a row is inserted into a table and a primary or unique key already exists, the following happens:

• If the INSERT statement contains a DUPLICATE KEY UPDATE clause, then the existing row is updated using the value provided in the update clause.
• If the INSERT statement does not contain a DUPLICATE KEY UPDATE clause, an error is generated.

Example

Consider the following form:

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

We would like to combine the following updates into a single query:

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;

The corresponding batch update query using INSERT ... ON DUPLICATE KEY UPDATE is as follows:

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 inserts a new row if it does not already exist, or updates an existing row if the primary key already exists in the table.

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