在多个项目的重复键更新上插入.. [英] INSERT INTO .. ON DUPLICATE KEY UPDATE for multiple items

问题描述

我想做这样的事情

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和t.b是键.这一切都很好，但第二次插入时出现错误.使用phpMyAdmin这样的查询可以正常工作，但是我猜它正在独立运行查询，因为它将打印出该查询的结果作为注释?

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.

如果还有另一种方式来执行如果存在则进行更新，否则使用两个键在多个字段上进行插入"查询，我也想知道其他想法.我想我可以分别运行每个查询(例如phpMyAdmin?)，但这将是很多查询，因此我真的想避免这种情况.

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.

推荐答案

使用VALUES()函数

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)

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

这篇关于在多个项目的重复键更新上插入..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！