Use the VALUES() function

INSERT INTO t (t.a, t.b, t.c)
VALUES ('key1','key2','value'), ('key1','key3','value2')
ON DUPLICATE KEY UPDATE
t.c = VALUES(t.c)

see http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Answer from ʞɔıu on Stack Overflow
Discussions

mysql - INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query - Database Administrators Stack Exchange
I've come across people recommending using ON DUPLICATE KEY UPDATE to UPDATE multiple rows using a single query. Unfortunately no one seems to be using clean SQL (defining an obvious primary key of... More on dba.stackexchange.com
🌐 dba.stackexchange.com
February 4, 2020
node.js - nodejs mysql bulk INSERT on DUPLICATE KEY UPDATE - Stack Overflow
I am trying to insert around 1000 rows with one single mysql statement and update the row if the key already exists. I doing this in nodejs using this module. My code currently looks like this: ... More on stackoverflow.com
🌐 stackoverflow.com
mysql - Bulk INSERT ... ON DUPLICATE KEY UPDATE inserts only a small fraction of rows in MariaDB - Database Administrators Stack Exchange
I have a table like the following: CREATE TABLE `demo_upsert` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci', `LastListID` BIGINT(20) NULL More on dba.stackexchange.com
🌐 dba.stackexchange.com
May 30, 2020
sql - MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query - Stack Overflow
I have a SQL query where I want to insert multiple rows in single query. so I used something like: $sql = "INSERT INTO beautiful (name, age) VALUES ('Helen', 24), ('Katrina', 21), ('Samia'... More on stackoverflow.com
🌐 stackoverflow.com
People also ask

How does REPLACE INTO differ from INSERT ON DUPLICATE KEY UPDATE
ANS: REPLACE INTO deletes the old record entirely before inserting the new one, whereas ON DUPLICATE KEY UPDATE modifies the existing record in place. REPLACE can cause issues with foreign key constraints due to the deletion step.
🌐
sqlpey.com
sqlpey.com › mysql › sql-bulk-update-strategies
SQL Bulk Update Strategies: Performance and Best Practices for ...
What happens if ON DUPLICATE KEY UPDATE encounters a NOT NULL constraint violation
ANS: If a row update attempt fails due to a NOT NULL column missing a value (and no default is set), MySQL might raise a warning or error depending on the SQL mode, even if you didn’t explicitly list that column for update.
🌐
sqlpey.com
sqlpey.com › mysql › sql-bulk-update-strategies
SQL Bulk Update Strategies: Performance and Best Practices for ...
Can I update two separate tables in one SQL command
ANS: Yes, MySQL allows you to list multiple tables in the UPDATE statement separated by commas, defining the specific updates for each aliased table in the SET clause, linked by a WHERE condition.
🌐
sqlpey.com
sqlpey.com › mysql › sql-bulk-update-strategies
SQL Bulk Update Strategies: Performance and Best Practices for ...
🌐
Stack Exchange
dba.stackexchange.com › questions › 258914 › insert-on-duplicate-key-update-to-update-multiple-rows-in-single-query
mysql - INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query - Database Administrators Stack Exchange
February 4, 2020 - Every single column must be defined for ON DUPLICATE KEY UPDATE to work! I effective had columns id, a, b, c and d in the table. I forgot to define one of the columns (that wasn't id as that is the first half of the requirement). ... What column is missing? Why does it matter? Please either make the question and answer fit together for the benefit of other readers. (Else delete the Question.) ... The first one will surely work as well. The only difference is that it will update a, c and d but not column b.
🌐
MariaDB
mariadb.com › kb › en › insert-on-duplicate-key-update
INSERT ON DUPLICATE KEY UPDATE | Server | MariaDB Documentation
ALTER TABLE ins_duplicate ADD id2 INT; UPDATE ins_duplicate SET id2=id+10; ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2); ... INSERT INTO ins_duplicate VALUES (2,'Lion',13) ON DUPLICATE KEY UPDATE animal='Lion'; Query OK, 2 rows affected (0.004 ...
🌐
MySQL
dev.mysql.com › doc › refman › 8.0 › en › insert-on-duplicate.html
MySQL :: MySQL 8.0 Reference Manual :: 15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement
January 13, 2022 - The first row contains a duplicate value for one of the table's unique keys (column a), but b=b+1 in the UPDATE clause results in a unique key violation for column b; the statement is immediately rejected with an error, and no rows are updated. Let us repeat the statement, this time adding the IGNORE keyword, like this: mysql> INSERT IGNORE INTO t VALUES ROW(2,3), ROW(3,3) -> ON DUPLICATE KEY UPDATE a=a+1, b=b-1; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 2 Duplicates: 1 Warnings: 1
🌐
MySQL
dev.mysql.com › doc › en › insert-on-duplicate.html
MySQL :: MySQL 8.4 Reference Manual :: 15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement
The first row contains a duplicate value for one of the table's unique keys (column a), but b=b+1 in the UPDATE clause results in a unique key violation for column b; the statement is immediately rejected with an error, and no rows are updated. Let us repeat the statement, this time adding the IGNORE keyword, like this: mysql> INSERT IGNORE INTO t VALUES ROW(2,3), ROW(3,3) -> ON DUPLICATE KEY UPDATE a=a+1, b=b-1; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 2 Duplicates: 1 Warnings: 1
Find elsewhere
🌐
MySQL
bugs.mysql.com › 97027
MySQL Bugs: #97027: Bulk insert with ON DUPLICATE KEY UPDATE breaks replicaiton
September 26, 2019 - 5.7.27 to 5.7.27 works ok master [localhost:19428] {root} (test) > CREATE TABLE `t1` ( -> `id` int(11) DEFAULT NULL, -> `test` varchar(255) DEFAULT 'hallo', -> UNIQUE KEY `id` (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) master [localhost:19428] {root} (test) > INSERT INTO `t1` VALUES (1,'foo'),(2,'test'),(3,'lorem'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 master [localhost:19428] {root} (test) > insert into t1 values(1, 'test'), (1, 'bar') on duplicate key update test=values(test); Query OK, 4 rows affected (0.01 sec)
🌐
sqlpey
sqlpey.com › mysql › sql-bulk-update-strategies
SQL Bulk Update Strategies: Performance and Best Practices for Multiple Records
November 4, 2025 - This approach is frequently cited ... a table with an id primary key: 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`);...
🌐
MySQL
forums.mysql.com › read.php
Bulk Insert On Duplicate Key Update Performance
July 9, 2007 - Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party
🌐
Stack Exchange
dba.stackexchange.com › questions › 268183 › bulk-insert-on-duplicate-key-update-inserts-only-a-small-fraction-of-rows-in
mysql - Bulk INSERT ... ON DUPLICATE KEY UPDATE inserts only a small fraction of rows in MariaDB - Database Administrators Stack Exchange
May 30, 2020 - UPDATE: I've implemented a reproducer program and submitted a bug https://jira.mariadb.org/browse/CONC-472 ... Out of ~15K rows for insert, I'm getting ~200 rows actually affected. Affected == which's values were really changed, i.e. only those duplicates which's old LastListID value was NOT 9. ... @Akina, according to the documentation, MariaDB/MySQL must report each inserted row as 1 and each updated row as 2.
🌐
Plus2Net
plus2net.com › sql_tutorial › sql_update-on-duplicate-key.php
Update multiple records by using ON DUPLICATE KEY UPDATE in MySQL
February 5, 2000 - INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES (2, 'Max Ruin', 'Three', 86, 57, 86) on duplicate key update social=86,science=57,math=86 We will get a message saying 2 rows inserted, but actually we have updated one record only. Here MySQL will return the number of affected rows based on the action it performed.
🌐
Prisma
prisma.io › dataguide › mysql › inserting-and-modifying-data › insert-on-duplicate-key-update
ON DUPLICATE KEY UPDATE to upsert and modify data in MySQL
The ON DUPLICATE KEY UPDATE clause allows us to do this: INSERT INTO director (id, name) VALUES (3, 'susan') ... MySQL considers an ON DUPLICATE KEY UPDATE where an update occurs to the existing row as two rows affected.
🌐
Alibaba Cloud
alibabacloud.com › help › en › analyticdb-for-mysql › developer-reference › insert-on-duplicate-key-update
INSERT ON DUPLICATE KEY UPDATE to insert or update data - AnalyticDB - Alibaba Cloud Documentation Center
January 28, 2023 - If you execute the INSERT ON DUPLICATE KEY UPDATE statement to update a large amount of data or update data at a high frequency (more than 100 QPS), the CPU utilization may significantly increase. We recommend that you execute the REPLACE INTO statement to update data in batches. For more ...
🌐
oinume journal
journal.lampetty.net › top › mysql
MySQLでbulk insert + on duplicate key updateしたい - oinume journal
November 9, 2015 - INSERT INTO users VALUES ('akuwano', 15), ('oinume', 14), ('oranie', 13) ON DUPLICATE KEY UPDATE age = VALUES(age); mysql> SELECT * FROM users; +---------+-----+ | name | age | +---------+-----+ | akuwano | 15 | | oinume | 14 | | oranie | 13 | +---------+-----+
🌐
MySQL Tutorial
mysqltutorial.org › home › mysql basics › mysql insert on duplicate key update statement
MySQL INSERT ON DUPLICATE KEY UPDATE Statement
December 27, 2023 - INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) AS new_data -- Row alias ON DUPLICATE KEY UPDATE column1 = new_data.column1, column2 = new_data.column2 + 1;Code language: SQL (Structured Query Language) (sql) MySQL also allows you to assign aliases to columns to avoid ambiguity, especially with long column names:
🌐
Reddit
reddit.com › r/mysql › how to run insert query with on duplicate key update in a single batch
r/mysql on Reddit: How to run INSERT query with ON DUPLICATE KEY UPDATE in a single batch
January 18, 2020 -

Hello,

I want to insert multiple rows in a single query, which have ON DUPLICATE KEY UPDATE statements for each row.

I want to do this for performance reasons, I can achieve this by running all insert queries in a loop from application code, but that'll be too heavy.

MySQL => 5.7

Application code =>Python 3.6

🌐
GitHub
github.com › mysqljs › mysql › issues › 1248
Syntax help for Bulk insert with ON DUPLICATE · Issue #1248 · mysqljs/mysql
October 19, 2015 - qStatus = []; qStatus.push([33, 'a', 'a1']) qStatus.push([33, 'a', 'a2']) qStatus.push([33, 'b', 'a3']) //this works in mysql workbench INSERT INTO status(id, keyname, val) VALUES(3, "ut", "a4"), (3, "ut", "a5") ON DUPLICATE KEY UPDATE val = VALUES(val); var s = 'INSERT INTO status(id, keyname, val) VALUES(?) ON DUPLICATE KEY UPDATE val = VALUES(val)'; dbioCore.loggingpool.query(s, qStatus, function (err, res) { if (err) { logger.error("Failure bulk inserting status data" + err); } qStatus = []; //clear queue ·
Author   Laro88