How Can I Use MySQL's ON DUPLICATE KEY UPDATE for Multiple Row Inserts and Updates?

Efficiently Inserting and Updating Multiple Rows in MySQL using ON DUPLICATE KEY UPDATE

MySQL's ON DUPLICATE KEY UPDATE clause provides a streamlined method for performing both insertion and update operations on multiple rows within a single query. This is particularly useful when dealing with unique keys, preventing errors and ensuring data consistency.

Scenario:

Consider a scenario where you need to insert multiple rows into a table, but some rows might already exist due to duplicate unique key values. Instead of generating errors, you want to update the existing rows with the new data.

Example:

Let's say you have the following SQL statement designed to insert several rows into a table named beautiful:

<code class="language-sql">INSERT INTO beautiful (name, age) VALUES ('Helen', 24), ('Katrina', 21), ('Samia', 22), ('Hui Ling', 25), ('Yumie', 29);</code>

If name is a unique key and some names already exist, this query will fail. To handle this, we utilize ON DUPLICATE KEY UPDATE.

Solution:

Append the ON DUPLICATE KEY UPDATE clause to your INSERT statement:

<code class="language-sql">INSERT INTO beautiful (name, age) VALUES ('Helen', 24), ('Katrina', 21), ('Samia', 22), ('Hui Ling', 25), ('Yumie', 29) ON DUPLICATE KEY UPDATE age = VALUES(age);</code>

This modified query will insert new rows where the name is unique. If a duplicate name is encountered, only the age column will be updated to the value provided in the VALUES clause.

Modern Syntax (MySQL 8.0.19 and later):

MySQL 8.0.19 and later versions offer a more readable approach using aliases:

<code class="language-sql">INSERT INTO beautiful (name, age) VALUES ('Helen', 24), ('Katrina', 21), ('Samia', 22), ('Hui Ling', 25), ('Yumie', 29) AS new ON DUPLICATE KEY UPDATE age = new.age;</code>

Here, AS new assigns an alias to the new row, allowing you to clearly reference its fields (e.g., new.age).

Older Syntax (MySQL versions prior to 8.0.19):

For older MySQL versions, the VALUES keyword is used to refer to the values being inserted:

<code class="language-sql">INSERT INTO beautiful (name, age) VALUES ('Helen', 24), ('Katrina', 21), ('Samia', 22), ('Hui Ling', 25), ('Yumie', 29) ON DUPLICATE KEY UPDATE age = VALUES(age);</code>

By using ON DUPLICATE KEY UPDATE, you can efficiently manage both insertions and updates within a single SQL statement, simplifying your code and improving database performance.

The above is the detailed content of How Can I Use MySQL's ON DUPLICATE KEY UPDATE for Multiple Row Inserts and Updates?. For more information, please follow other related articles on the PHP Chinese website!