CODEWITHSUNDEEP
mysql
soonic
soonic

Reputation: 615

insert on duplicate key update with two contitions

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?

Upvotes: 1

Views: 179

Answers (2)

Blaztix
Blaztix

Reputation: 1294

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.

Upvotes: 3

num8er
num8er

Reputation: 19372

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);

Upvotes: 2

Related Questions