How to Use MySQL's ON DUPLICATE KEY UPDATE for Multiple Rows?

Efficiently Handling Multiple Rows with MySQL's ON DUPLICATE KEY UPDATE

MySQL offers a streamlined approach to managing multiple row insertions while simultaneously checking for and updating existing unique values. This is accomplished using the ON DUPLICATE KEY UPDATE clause. This feature allows for conditional updates or insertions of specific fields within a single query.

To selectively update existing rows and add new ones, leverage an alias or the VALUES keyword in conjunction with ON DUPLICATE KEY UPDATE.

MySQL 8.0.19 and later (Using an Alias):

<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>

MySQL 8.0.19 and earlier (Using the VALUES Keyword):

<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>

These examples demonstrate how to update or insert data based on existing unique keys. If a unique key already exists, the specified fields are updated; otherwise, a new row is inserted. The VALUES keyword or the alias new references the values being inserted in the INSERT statement.

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