开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜