insert on duplicate key update with two contitions

Question

This is the EDITED version of my first post because it seemed to be not enough clear the first one.

I want to insert or update if exists depending on a SELECT query result. This is my code working in a standard way:

$query = "SELECT * FROM table1 WHERE id1 =".$id1." AND id2=".$id2;
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
    $query = "UPDATE table1 SET id3=".$id3." WHERE id1 =".$id1." AND id2 = ".$id2." LIMIT 1";
    $result = mysqli_query($conn, $query);
} else {
    $query = "INSERT INTO table1 (id1, id2, id3) VALUES ($id1, $id2, $id3)";
    $result = mysqli_query($conn, $query);
}

id1, id2, id2 values can be repeated but under one condition (id1 and id2 can't be repeated at the same time.

For example:

a new row can be inserted only in a situation like this:

if this one already exists in the table1

id0=0 id1=5 id2=10 id3=15

then the next one would be:

id0=1 id1=6 id2=10 id3=15 or

id0=1 id1=5 id2=11 id3=15

a row would be updated when

if this one already exists in the table1

id0=0 id1=5 id2=10 id3=15

and the next one would be:

id0=1 id1=5 id2=10 id3=20

as you notice the id1 and id2 is the same as the one already in the table, so id3 would be updated.

Also my table1 looks like this:

CREATE TABLE table1 (
  `id0` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `id3` int(11) NOT NULL
) ENGINE=InnoDB;

MY QUESTION IS: Is it possible to combine the above 3 queries into one using "insert on duplicate key update" or any other way?

Blaztix · Accepted Answer · 2016-08-18T16:04:55.947

3

First you need to set the key, id1 and id2 as one (combination is unique)

CREATE TABLE table1 (
  `id0` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `id3` int(11) NOT NULL,
  UNIQUE KEY `KeyName` (`id1`,`id2`) # <------
) ENGINE=InnoDB;

In this way, you unable to add two rows that contain the same 'id1' and 'id2', and only then work the below command.

INSERT INTO table1 (id1, id2, id3) 
            VALUES ($id1, $id2,$id3)
ON DUPLICATE KEY UPDATE id3 = $id3;

UPDATE

When we perform this method on a innoDB table, there is a bug that generates a gap in theAUTO_INCREMENT

if we use InnoDB

    CREATE TABLE table1 (
      `id0` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `id1` int(11) NOT NULL,
      `id2` int(11) NOT NULL,
      `id3` int(11) NOT NULL,
      UNIQUE KEY `KeyName` (`id1`,`id2`) # <------
    ) ENGINE=InnoDB;

    INSERT INTO table1 (id1, id2, id3) 
                VALUES (5, 10,1)
    ON DUPLICATE KEY UPDATE id3 = 1;

    INSERT INTO table1 (id1, id2, id3) 
                VALUES (5, 10,2)
    ON DUPLICATE KEY UPDATE id3 = 2;

    INSERT INTO table1 (id1, id2, id3) 
                VALUES (6, 10,1)
    ON DUPLICATE KEY UPDATE id3 = 1;

Let the following result:

+-----+-----+-----+-----+
| id0 | id1 | id2 | id3 |
+-----+-----+-----+-----+
| 1   | 5   | 10  | 2   |
+-----+-----+-----+-----+
| 3   | 6   | 10  | 1   |
+-----+-----+-----+-----+

But if we use Myisam

    CREATE TABLE table2 (
      `id0` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `id1` int(11) NOT NULL,
      `id2` int(11) NOT NULL,
      `id3` int(11) NOT NULL,
      UNIQUE KEY `KeyName` (`id1`,`id2`) # <------
    ) ENGINE=Myisam;

    INSERT INTO table2 (id1, id2, id3) 
                VALUES (5, 10,1)
    ON DUPLICATE KEY UPDATE id3 = 1;

    INSERT INTO table2 (id1, id2, id3) 
                VALUES (5, 10,2)
    ON DUPLICATE KEY UPDATE id3 = 2;

    INSERT INTO table2 (id1, id2, id3) 
                VALUES (6, 10,1)
    ON DUPLICATE KEY UPDATE id3 = 1;

Let the following result:

+-----+-----+-----+-----+
| id0 | id1 | id2 | id3 |
+-----+-----+-----+-----+
| 1   | 5   | 10  | 2   |
+-----+-----+-----+-----+
| 2   | 6   | 10  | 1   |
+-----+-----+-----+-----+

But this error should not be annoying and if necessary use InnoDB, this only renumber the rows and the program should know that there are gaps, it is the same effect of removing a row.

Although, it can be performed by php, but only generates more load on the server.

edited Aug 18 '16 at 16:04

answered Aug 18 '16 at 01:14

Blaztix

1,223
1
19
28

First: I get an error when creating the table "Invalid default value for 'id2'. Second: I can't use `UNIQUE KEY` because id1 and id2 can be repeated as long as they aren't both duplicated if they are it means that record exists and should be updated the id3 only. Third: if I want to set PRIMARY KEY id1 and id2 I get an err: "#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key" because as I mentioned I have id0 auto-increment. So, from what you've said It seems that I can't use `ON DUPLICATE KEY UPDATE`. – soonic Aug 18 '16 at 06:31
Is there any other way I can do this in one query? (check the condition of row existence `WHERE id1=".$id1." AND id2 = ".$id2."` and insert or update) – soonic Aug 18 '16 at 06:33
@soonic how about using REPLACE INTO? Have You tried? – num8er Aug 18 '16 at 08:28
@num8er no I haven't used REPLACE INTO. Can you add to your answer, how it should look in my situation? I've read somewhere that this works on update like this: it deletes existing row then inserts new one with new values, so my column with auto increment id0 will be missing an id. Is that true? (I would not like this way to work) – soonic Aug 18 '16 at 09:20
Just replace INSERT word to REPLACE – num8er Aug 18 '16 at 09:22
Maybe You should try to run: INSERT IGNORE together with UPDATE WHERE – num8er Aug 18 '16 at 09:30
@num8er It doesn't make sense just replacing the word INSERT with REPLACE because I need a condition for checking the row existence like something like this: `SELECT * FROM table1 WHERE id1=".$id1." AND id2 = ".$id2."; if (mysqli_num_rows($result) > 0)` then update else insert – soonic Aug 18 '16 at 09:34
When You have proper unique index replace into will do checking for You. Make sure that You've set proper combined index on id1 and id2 – num8er Aug 18 '16 at 09:36
Please check the update, before i used an incomplete example – Blaztix Aug 18 '16 at 12:27
When you set key as two variables does not mean that the column can not be repeated, means that the combination can not be repeated, database do that, no php – Blaztix Aug 18 '16 at 12:56
thank you for reply. Now it works as it should be referring to my question, but it turned out for me that I don't like the way its doing this. Every update is consuming next value of auto-increment for id0 column. It behaves like REPLACE. Probably this is the way how ON DUPLICATE KEY UPDATE works. – soonic Aug 18 '16 at 13:33
About that, http://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql "You should not depend on auto_increment having no gaps. If that is a requirement, the overhead on the updates and inserts is much larger." – Blaztix Aug 18 '16 at 14:39
Thank you for you answer and update. It's very well explained! – soonic Aug 18 '16 at 16:16

num8er · Answer 2 · 2016-08-18T12:52:50.807

2

I can give You 3 examples:

1) handling on duplicate key event:

INSERT INTO table1 (id1, id2, id3) VALUES ('".$id1."', '".$id2."','".$id3."')
  ON DUPLICATE KEY UPDATE id3='".$id3."'";

2) replace into:

REPLACE INTO table1 (id1, id2, id3) VALUES ('".$id1."', '".$id2."','".$id3."');

3) insert ignore with update at same time:

$q = "INSERT IGNORE INTO table1 (id1, id2, id3) VALUES ('".$id1."', '".$id2."','".$id3."');
    UPDATE table1 SET id3 = ".$id3." WHERE id1=".$id1." AND id2 = ".$id2." LIMIT 1;";

P.s. Make sure You've set proper combined index with id1 and id2

In case of none of these examples do not work:

$query = "SELECT 1 FROM table1 WHERE id1 =".(int)$id1." AND id2=".(int)$id2." LIMIT 1"; 
$result = mysqli_query($conn, $query); 

$query = (mysqli_num_rows($result) > 0) ?
    "UPDATE table1 SET id3=".$id3." WHERE id1 =".$id1." AND id2 = ".$id2." LIMIT 1" : 
    "INSERT INTO table1 (id1, id2, id3) VALUES (".(int)$id1.", ".(int)$id2.", ".(int)$id3.")";
mysqli_query($conn, $query);

edited Aug 18 '16 at 12:52

answered Aug 17 '16 at 22:13

num8er

18,604
3
43
57

This will not work in my situation because **id0** is auto increment column and there will be always unique value, so I will have duplicated **id1 and id2** at the same time. In example: `id0=0 id1=5 id2=10 id3=15` and for `id0=1 id1=5 and id2=10 id3=20` which is not correct for me. The **id3** only should be updated when such a situation appears. The correct situation for inserting a row would be if this one already exists `id0=0 id1=5 id2=10 id3=15` next one: `id0=1 id1=6 id2=10 id3=15` or `id0=1 id1=5 id2=11 id3=20`. – soonic Aug 18 '16 at 06:53
Thank you for reply, but it still doesn't help me. The 3 example has some syntax error. So, I changed my post question and I hope it's more clear now. – soonic Aug 18 '16 at 11:10
Actually, don't know what to say. Mostly tricks are done by 3 examples. Sometimes people write stored procedures, but I don't see big performance gain with procedures for Your question. Sometimes people make queue table and insert all operations there and backend worker does hard stuff at "backstage" to not to make user wait for some data handled by db. – num8er Aug 18 '16 at 12:50

insert on duplicate key update with two contitions

2 Answers2