INSERT INTO or UPDATE with two conditions

by

that is what insert on duplicate key update is for.

The Manual page for it is here.

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)

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

Here is a complete example

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;

show results

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

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

More Related Contents:

  1. Which DB technology is sued to solve the following search issue? [closed]
  2. MySQL pivot row into dynamic number of columns
  3. Rank function in MySQL
  4. Count the number of occurrences of a string in a VARCHAR field?
  5. Find nearest latitude/longitude with an SQL query
  6. MySQL – Operand should contain 1 column(s)
  7. Restore table structure from frm and ibd files
  8. mysql pivot/crosstab query
  9. MySQL: Split comma separated list into multiple rows
  10. Most efficient way to get table row count
  11. UNION query with codeigniter’s active record pattern
  12. Subqueries with EXISTS vs IN – MySQL
  13. What is this operator in MySQL?
  14. Remove Primary Key in MySQL
  15. 60 million entries, select entries from a certain month. How to optimize database?
  16. Get most recent row for given ID
  17. Is it possible to GROUP BY multiple columns using MySQL?
  18. MySQL monthly Sale of last 12 months including months with no Sale
  19. Is merge statement available in MySQL
  20. Mysql covering vs composite vs column index
  21. MYSQL syntax not evaluating not equal to in presence of NULL
  22. MySQL: LAST_INSERT_ID() returns 0
  23. Calculating a Moving Average MySQL?
  24. MySQL INNER JOIN select only one row from second table
  25. Storing IPv6 Addresses in MySQL
  26. Insert/ Update random date in MySQL
  27. Replace null with 0 in MySQL
  28. Get total hours worked in a day mysql
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment