INSERT INTO .. ON DUPLICATE KEY UPDATE for multiple items

Mysql

Mysql Problem Overview


I want to do something like this

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

t.a and t.b are keys. This all works fine but i get an error on the second insert. With phpMyAdmin a query like this works fine but i'm guessing it's running the queries independently as it prints out the results from that query as comments?

Something like this would be good too but i will need to have different values for each item. I prefer this but i'm not sure how i can change the value on the update for each value.

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

The problem is in the question marks, what should i put there so that each insert/update will have the correct value? Obviously if i put a value there all the fields will get that value.

If there is another way of doing an "update if exists, otherwise insert" query on multiple fields with two keys, i'm up for other ideas too. I guess i could run each query separately (like phpMyAdmin?) but it's going to be a lot of queries so i really want to avoid that.

Mysql Solutions


Solution 1 - Mysql

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

Solution 2 - Mysql

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)

Hope that helps someone out there looking to perform bulk insert with multiple on duplicate key update. The syntax escaped me.

Solution 3 - Mysql

After MySQL 8.0.19, you can use as keyword, for example:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

or

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

ref: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAnttiView Question on Stackoverflow
Solution 1 - MysqlʞɔıuView Answer on Stackoverflow
Solution 2 - MysqlMarcView Answer on Stackoverflow
Solution 3 - MysqlHungerView Answer on Stackoverflow