INSERT INTO .. ON DUPLICATE KEY UPDATE for multiple items
MysqlMysql 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