INSERT INTO或UPDATE有两个条件 [英] INSERT INTO or UPDATE with two conditions

问题描述

乍看之下，这个问题似乎很简单，但我只是没有找到合理的解决方案.

This problem seems easy at first sight, but I simply have not found a solution that is reasonable time wise.

考虑具有以下特征的表:

Consider a table with the following characteristics:

ID INTEGER PRIMARY KEY AUTOINCREMENT
name INTEGER
values1 INTEGER
values2 INTEGER
dates DATE

每天，为将来的日期生成N个新行，并且名称"来自有限列表.我想在有新数据时插入新行，但是如果已经有一个包含名称"和日期"的行，只需对其进行更新.

Every day, N amount of new rows are generated, for dates into the future, and with the 'name' coming from a finite list. I would like to insert a new row when there is new data, but if there is already a row with 'name' and 'dates', simply update it.

请注意，当前提议的检查条件的SPROC解决方案不可行，因为这是从另一种语言推送数据.

Please note that a current proposed solution of an SPROC that checks the conditional is not feasible, as this is data being pushed from another language.

推荐答案

是insert on duplicate key update的目的.

其手册"页面位于此处.

诀窍在于，表需要具有唯一键(可以是复合键)，以便可以检测出执行插入操作的clash.这样，将在该行上进行更新，否则将进行插入.当然，它可以是主键.

The trick is that the table needs to have a unique key (can be a composite) so that the clash of doing an insert can be detected. As such, the update to occur on that row, otherwise an insert. It can be a primary key, of course.

在您的情况下，您可以使用一个复合键，例如

In your case, you could have a composite key such as

unique key(theName,theDate)

如果该行已经存在，则检测到clash，并进行更新.

If the row is already there, the clash is detected, and the update happens.

create table myThing
(   id int auto_increment primary key,
    name int not null,
    values1 int not null,
    values2 int not null,
    dates date not null,
    unique key(name,dates) -- <---- this line here is darn important
);

insert myThing(name,values1,values2,dates) values (777,1,1,'2015-07-11') on duplicate key update values2=values2+1;
insert myThing(name,values1,values2,dates) values (778,1,1,'2015-07-11') on duplicate key update values2=values2+1;
-- do the 1st one a few more times:
insert myThing(name,values1,values2,dates) values (777,1,1,'2015-07-11') on duplicate key update values2=values2+1;
insert myThing(name,values1,values2,dates) values (777,1,1,'2015-07-11') on duplicate key update values2=values2+1;
insert myThing(name,values1,values2,dates) values (777,1,1,'2015-07-11') on duplicate key update values2=values2+1;

显示结果

select * from myThing;
+----+------+---------+---------+------------+
| id | name | values1 | values2 | dates      |
+----+------+---------+---------+------------+
|  1 |  777 |       1 |       4 | 2015-07-11 |
|  2 |  778 |       1 |       1 | 2015-07-11 |
+----+------+---------+---------+------------+

按预期，在重复的密钥更新上插入，只有2行.

As expected, insert on duplicate key update works, just 2 rows.

这篇关于INSERT INTO或UPDATE有两个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！