INSERT INTO .. ​​ON DUPLICATE KEY UPDATE for multiple items

Question

I want to do something like this

INSERT INTO t (ta, tb, tc) VALUES ('key1','key2','value') ON DUPLICATE KEY UPDATE tc = 'value'; INSERT INTO t (ta, tb, tc) VALUES ('key1','key3','value2') ON DUPLICATE KEY UPDATE tc = 'value2'; 

ta and tb are keys. It all works fine, but I get an error in the second insert. With phpMyAdmin, such a query works fine, but I assume that it runs the queries independently, since it outputs the results from this query as comments?

Something like this will be fine too, but I will need to have different values ​​for each element. I prefer this, but I'm not sure how I can change the update value for each value.

 INSERT INTO t (ta, tb, tc) VALUES ('key1','key2','value'), ('key1','key3','value2') ON DUPLICATE KEY UPDATE tc = ??? 

The question mark is the problem, what should I put there so that each insert / update has the correct meaning? Obviously, if I put a value there, all fields will get that value.

If there is another way to perform the “update, if there is, otherwise insert” a request for several fields with two keys, I will also think about other ideas. I think I could execute each request separately (e.g. phpMyAdmin?), But it will be a lot of requests, so I really want to avoid this.

Answer 1

Use the VALUES () function

 INSERT INTO t (ta, tb, tc) VALUES ('key1','key2','value'), ('key1','key3','value2') ON DUPLICATE KEY UPDATE tc = VALUES(tc) 

see http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Answer 2

Reputation is too low for comments, but I wanted to add a bit more complex syntax that was inspired by @ ʞɔıu's answer. To update multiple fields on a duplicate key:

 INSERT INTO t (ta, tb, tc, td) VALUES ('key1','key2','value','valueb'), ('key1','key3','value2','value2b') ON DUPLICATE KEY UPDATE tc = VALUES(tc), td = VALUES(td) 

Hope someone out there wants to do a bulk insert with a few re-key updates. The syntax eluded me.