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)
How to run INSERT query with ON DUPLICATE KEY UPDATE in a single batch
sql - MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query - Stack Overflow
php - On Duplicate Key Update - Multiple Columns - Stack Overflow
mysql - On Duplicate Key Update same as insert - Stack Overflow
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
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.
Well, this is old. But of course you only need to provide a value once, there's no reason to add it a second time in the query (which comes in handy for multiple inserts, or prepared statements):
INSERT INTO table1
(col1, col2, col3, col4)
VALUES
('val1', 'val2', 'val3', 'val4')
ON DUPLICATE KEY UPDATE
col2=VALUES(col2),
col3=VALUES(col3) [,...]
Which has as advantage it will still work for a multiple insert statement:
INSERT INTO table1
(col1, col2, col3, col4)
VALUES
('val1', 'val2', 'val3', 'val4'),
('val5', 'val6', 'val7', 'val8'),
('val9', 'val10', 'val11', 'val12')
ON DUPLICATE KEY UPDATE
col2=VALUES(col2),
col3=VALUES(col3) [,...]
A modern PHP example:
$sql = "INSERT INTO table1 (col1, col2, col3, col4) VALUES (?,?,?,?)
ON DUPLICATE KEY UPDATE
col2=VALUES(col2),
col3=VALUES(col3),
col4=VALUES(col4)";
$db->prepare($sql)->execute([$val1, $val2, $val3, $val4]);
INSERT INTO table1
(`col1`, `col2`, `col3`, `col4`)
VALUES
('val1', 'val2', 'val3', 'val4')
ON DUPLICATE KEY UPDATE
`col2`='val2',
`col3`='val3', [...]
I fixed your quotes and tickmarks.
The UPDATE statement is given so that older fields can be updated to new value. If your older values are the same as your new ones, why would you need to update it in any case?
For eg. if your columns a to g are already set as 2 to 8; there would be no need to re-update it.
Alternatively, you can use:
INSERT INTO table (id,a,b,c,d,e,f,g)
VALUES (1,2,3,4,5,6,7,8)
ON DUPLICATE KEY
UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;
To get the id from LAST_INSERT_ID; you need to specify the backend app you're using for the same.
For LuaSQL, a conn:getlastautoid() fetches the value.
There is a MySQL specific extension to SQL that may be what you want - REPLACE INTO
However it does not work quite the same as 'ON DUPLICATE UPDATE'
It deletes the old row that clashes with the new row and then inserts the new row. So long as you don't have a primary key on the table that would be fine, but if you do, then if any other table references that primary key
You can't reference the values in the old rows so you can't do an equivalent of
INSERT INTO mytable (id, a, b, c) values ( 1, 2, 3, 4) ON DUPLICATE KEY UPDATE id=1, a=2, b=3, c=c + 1;
I'd like to use the work around to get the ID to!
That should work โ last_insert_id() should have the correct value so long as your primary key is auto-incrementing.
However as I said, if you actually use that primary key in other tables, REPLACE INTO probably won't be acceptable to you, as it deletes the old row that clashed via the unique key.
Someone else suggested before you can reduce some typing by doing:
INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);