80

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.

Antti
  • 3,119
  • 3
  • 24
  • 22

3 Answers3

173

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

ʞɔıu
  • 47,148
  • 35
  • 106
  • 149
  • 3
    mysql> INSERT INTO param(id,num) VALUES(3,4) ON DUPLICATE KEY UPDATE id=VALUES( d)+222; Query OK, 2 rows affected (0.67 sec) – zloctb Apr 22 '14 at 08:11
  • 1
    so basically values refers to the same values inserted but relatively ofc to the each inserted row – FantomX1 May 12 '21 at 06:55
51

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)
starball
  • 20,030
  • 7
  • 43
  • 238
Marc
  • 1,895
  • 18
  • 25
3

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

Hunger
  • 5,186
  • 5
  • 23
  • 29