INSERT INTO or UPDATE with two conditions

Question

This problem seems easy at first glance, but I just did not find a solution, a reasonable reasonable time.

Consider a table with the following characteristics:

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

Every day, N new lines are generated, dated in the future, and with a name derived from the final list. I would like to insert a new row when there is new data, but if there is already a row with names and dates, just refresh it.

Please note that the current proposed SPROC solution, which checks the conditional value, is not possible, since this data is being supplanted from another language.

Answer 1

for what insert on duplicate key update.

.

, ( ), clash . , , . , .

,

unique key(theName,theDate)

, clash .

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 .

Answer 2

:

• ,
• INSERT.. ON DUPLICATE KEY UPDATE