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 OverflowUse 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
Too low on rep for comment, but I wanted to add a slightly more complex syntax that was inspired by @ʞɔıu response. To update multiple fields on duplicate key:
INSERT INTO t (t.a, t.b, t.c, t.d)
VALUES ('key1','key2','value','valueb'), ('key1','key3','value2','value2b')
ON DUPLICATE KEY UPDATE
t.c = VALUES(t.c),
t.d = VALUES(t.d)
mysql - INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query - Database Administrators Stack Exchange
node.js - nodejs mysql bulk INSERT on DUPLICATE KEY UPDATE - Stack Overflow
mysql - Bulk INSERT ... ON DUPLICATE KEY UPDATE inserts only a small fraction of rows in MariaDB - Database Administrators Stack Exchange
sql - MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query - Stack Overflow
How does REPLACE INTO differ from INSERT ON DUPLICATE KEY UPDATE
What happens if ON DUPLICATE KEY UPDATE encounters a NOT NULL constraint violation
Can I update two separate tables in one SQL command
I would try an array of objects
[
{name:'Hy Dag3', points:'55040464', rank:'master', hotStreak:114,...},
{name:'Hkj', points:'554064', rank:'novice', hotStreak:14,...}
]
and then
this.conn.query("INSERT summoners SET ? " +
" ON DUPLICATE KEY UPDATE name = VALUES(name), rank = VALUES(rank)...
Because according to doc:
var post = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function (error, results, fields) {
if (error) throw error;
// Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'
try to put your sql_data array of arrays into another array like this
this.conn.query("...your query", [sql_data], (err) => {...})
so you match question mark in your statement, so if there was another one question mark, it would look like [sql_data, another_variable]
Beginning with MySQL 8.0.19 you can use an alias for that row (see reference).
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
...
For earlier versions use the keyword VALUES (see reference, deprecated with MySQL 8.0.20).
INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29)
ON DUPLICATE KEY UPDATE
age = VALUES(age),
...
I was looking for the same behavior using jdbi's BindBeanList and found the syntax is exactly the same as Peter Lang's answer above. In case anybody is running into this question, here's my code:
@SqlUpdate("INSERT INTO table_one (col_one, col_two) VALUES <beans> ON DUPLICATE KEY UPDATE col_one=VALUES(col_one), col_two=VALUES(col_two)")
void insertBeans(@BindBeanList(value = "beans", propertyNames = {"colOne", "colTwo"}) List<Beans> beans);
One key detail to note is that the propertyName you specify within @BindBeanList annotation is not same as the column name you pass into the VALUES() call on update.
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