# How to use `ON DUPLICATE KEY UPDATE` to upsert data in MySQL > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. The `INSERT...ON DUPLICATE KEY UPDATE` clause lets you handle scenarios where a record should be modified if it exists and added if it does not exist. Canonical URL: https://www.prisma.io/dataguide/mysql/inserting-and-modifying-data/insert-on-duplicate-key-update Authors: Justin Ellingwood Content revision: d63a2962984e4d7c087fa26169ff1009afa41a14fcb2bf7e1baeeeb77cc2fed6 Source revision: 1fe7ef3d9287b7c3aadd4cd0d2fe478dafd71727 Last updated: 2026-09-29 ## Introduction In many situations, you may want to ensure that a record exists in a table without a conflicting entry. Essentially, you want to find and modify the current record if it exists or add a new record with the values you want if one is not already present. This is typically referred to as an ["upsert" operation](https://www.prisma.io/dataguide/intro/database-glossary#upsert) (a combination of "insert" and "update"). MySQL allows you to perform this action using the `ON DUPLICATE KEY UPDATE` clause to modify the `INSERT` command. In this guide, we'll cover how to use this construct to update the values of an entry if it exists or else add it as a new row in the table. ## How to use the `INSERT...ON DUPLICATE KEY UPDATE` construct The basic syntax for the insert or update operation looks like this: ```sql INSERT INTO my_table (column1, column2) VALUES (value1, value2), (value3, value4), (value5, value6), (value7, value8) ON DUPLICATE KEY UPDATE = , = ; ``` Multiple columns can be provided after the `ON DUPLICATE KEY UPDATE` clause, each defining what the new value should be if there's a conflict with an existing record. To demonstrate this feature, we'll imagine a table called `director` with the following columns and populated data: ```sql CREATE TABLE director ( id SERIAL PRIMARY KEY, name VARCHAR(200) NOT NULL, latest_film VARCHAR(200) ); INSERT INTO director (name) VALUES ('frank'), ('bob'), ('sue'); ``` The data within the table looks like this: ```sql SELECT * FROM director; ``` ``` +----+-------+-------------+ | id | name | latest_film | +----+-------+-------------+ | 1 | frank | NULL | | 2 | bob | NULL | | 3 | sue | NULL | +----+-------+-------------+ 3 rows in set (0.00 sec) ``` If you attempt to insert another row with an `id` column of "3", MySQL will notify you of a conflict with the existing row: ```sql INSERT INTO director (id, name) VALUES (3, 'susan'); ``` ``` ERROR 1062 (23000): Duplicate entry '3' for key 'director.PRIMARY' ``` If we anticipate this possibility and want to update the existing row with the new information, we can avoid this error. The `ON DUPLICATE KEY UPDATE` clause allows us to do this: ```sql INSERT INTO director (id, name) VALUES (3, 'susan') ON DUPLICATE KEY UPDATE name = 'susan'; ``` ``` Query OK, 2 rows affected (0.00 sec) ``` MySQL considers an `ON DUPLICATE KEY UPDATE` where an update occurs to the existing row as two rows affected. If no conflict had occurred and the new record had been added, it would instead show one row affected. If an existing record was found but the columns already had the correct value, no rows would be reported as affected. The clause isn't limited to the primary key: a conflict with any `UNIQUE` index on the table triggers the update as well. If a new row conflicts with different existing rows on more than one unique index, MySQL only updates one of them, so be careful using `ON DUPLICATE KEY UPDATE` on tables with several unique indexes. You can confirm the row has been updated with the new information by typing: ```sql SELECT * FROM director; ``` ``` +----+-------+-------------+ | id | name | latest_film | +----+-------+-------------+ | 1 | frank | NULL | | 2 | bob | NULL | | 3 | susan | NULL | +----+-------+-------------+ 3 rows in set (0.00 sec) ``` ## How to insert or update multiple records at once If you are attempting to insert or update multiple records at the same time, the value to set each column likely depends on which record or records conflicted. For instance if you are trying to insert four new rows, but the third row has an `id` column that conflicts with an existing record, you most likely want to update the existing row based on the data you had in mind for the third row. MySQL allows you to reference that proposed data by giving the new rows an [alias](https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html). Add `AS ` after the rows in the `VALUES` clause, and then refer to the proposed value for a column as `.` in the `ON DUPLICATE KEY UPDATE` clause. The basic syntax looks like this: ```sql INSERT INTO my_table (column1, column2) VALUES (value1, value2), (value3, value4), (value5, value6), (value7, value8) AS new ON DUPLICATE KEY UPDATE = new., = new.; ``` This tells MySQL to use the data that is associated with the conflicting row to update the values. Older code often uses the [`VALUES()` function](https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_values) for this instead, as in ` = VALUES()`. Row aliases were added in MySQL 8.0.19, and the `VALUES()` form has been deprecated since MySQL 8.0.20. It still works, but every statement that uses it produces a deprecation warning, so use a row alias in new code. To see how this works, let's go back to the `director` table from before: ```sql SELECT * FROM director; ``` ``` +----+-------+-------------+ | id | name | latest_film | +----+-------+-------------+ | 1 | frank | NULL | | 2 | bob | NULL | | 3 | susan | NULL | +----+-------+-------------+ 3 rows in set (0.00 sec) ``` Suppose we wanted to ensure that the following records exist in the table: ``` +----+--------+-------------+ | id | name | latest_film | +----+--------+-------------+ | 4 | meg | NULL | | 2 | robert | NULL | | 5 | tamara | NULL | +----+--------+-------------+ ``` We could create an `INSERT...ON DUPLICATE KEY UPDATE` statement that looks like this: ```sql INSERT INTO director (id, name) VALUES (4, 'meg'), (2, 'robert'), (5, 'tamara') AS new ON DUPLICATE KEY UPDATE name = new.name; ``` MySQL would accept the statement, inserting two new rows and updating one where it conflicted with an existing record (there is already a record with an `id` of "2"): ```text Query OK, 4 rows affected (0.01 sec) Records: 3 Duplicates: 1 Warnings: 0 ``` If you view the table's data, you can see that the two new rows appear as expected and the values of the conflicting row have been updated with the appropriate new information: ```sql SELECT * FROM director; ``` ``` +----+--------+-------------+ | id | name | latest_film | +----+--------+-------------+ | 1 | frank | NULL | | 2 | robert | NULL | | 3 | susan | NULL | | 4 | meg | NULL | | 5 | tamara | NULL | +----+--------+-------------+ 5 rows in set (0.00 sec) ``` ## Conclusion MySQL's `INSERT...ON DUPLICATE KEY UPDATE` construct allows you to insert data while avoiding conflicts with existing records. Combined with a row alias that refers to the values you tried to insert, you can use it to make contextual updates to records that already exist without issuing multiple statements. This powerful feature can help you minimize the amount of checks and conditional logic you have to use outside of your SQL statements.