Any way to do an "insert or update as appropriate" in MySQL?
I'm hoping to be able to insert/update multiple rows of a table in a single call, but the data I'm pushing into the database might be new and might not. Basically I have a table columns a,b,c. a,b is unique.
So if the existing table looks like:
a|b|c
-----
1|1|1
1|2|3
I wanted to do something like
INSERT INTO tbl (a,b,c) VALUES (1,1,2), (2,3,4), (5,6,7);
I was hoping something like
INSERT INTO tbl (a,b,c) VALUES (1,1,2), (2,3,4), (5,6,7) ON DUPLICATE KEY UPDATE c=c;
Would work but that just uses the 'c' currently in the DB. Is there a way to tell it to use the new value for c, or some other way to achieve the same thing? This example uses 3 sets of values, but my actual will use 20-30, which is why I'd rather not have to loop through if possible.
I'm aware of how I'd achieve it by looping through all the values, so only re开发者_StackOverflow中文版ally need guidance on if it's possible in a single, or less than n+1 or so queries.
INSERT INTO tbl (a,b,c) VALUES (1,1,2), (2,3,4), (5,6,7) ON DUPLICATE KEY UPDATE c=VALUES(c)
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
精彩评论