How to know whether a row was updated or inserted after ON DUPLICATE KEY UPDATE query

Question

I need to insert into a table or update values.

I need to know whether the data got updated or newly created.

Currently my logic is SELECT, if not exist INSERT, if exist UPDATE.

I need to count the rows updated and the rows inserted.

This is slow and I need a faster approach: Can I use INSERT or REPLACE without trying to select first, and let the PDO client return whether the row got updated or inserted?

I'm using PHP / PDO / MySQL

Update with pseudo code:

Current logic:

<?php
$rowExists = PDO SELECT by key

if ($rowExists)
    PDO UPDATE by key
    $updates++
else
    PDO INSERT
    $inserts++

Wanted logic:

<?php
PDO INSERT .. ON DUPLICATE KEY
$updates++ or $inserts++ according to what it actually did

@MarcB I know, but does it tell me whether it got updated or inserted? Please see my updated question with pseudo code. — Daniel W., Oct 28 '14 at 21:39
if it was inserted, then last_insert_id() would have the new ID for that new row. but this'd apply only to a SINGLE insertion. if you're using extended insert syntax, or a `insert ... select from` then all bets are off. — Marc B, Oct 28 '14 at 21:40
@YourCommonSense nice now you point the question to some old mysql answers..... very helpful.. — Daniel W., Apr 23 '19 at 08:28

Roy · Answer 1 · 2019-04-19T10:07:31.350

I am using MariaDB (or MySQL if you like) and was looking for a solution to this question and came across this post. It answers the question in part, but didn't quite resolve what I was trying to achieve. And that question as I read it from DanFromGermany is: how many records were added to the database and how many were updated in the database during my transaction or transactions.

So using some of the helpful elements in this post, I came up with this solution: In my actual code I have wrapped it all in try/catch where needed and assumed that $dbx is an instantiated PDO mysql database object.

/* 
    Get the current table row count 
      (it's our starting point, so any inserted row will increase the count)
*/

    $stmt = $dbx->query("SELECT COUNT(*) FROM `destination_table`");
    $row = $stmt->fetch(PDO::FETCH_NUM);
    $startCount = $row[0];
    $stmt = null;

/* 
    Set an iteration counter
     (
      I was using a prepared statement looping through
      INSERT / ON DUPLICATE KEY UPDATE, so want to check each loop for any change
     )
*/

    $countAnAction = 0;

/* 
  Prepare the INSERT with ON DUPLICATE KEY UPDATE for each line to process
   (assuming `a` is the key, regardless of type)
*/
    $stmt = $dbx->prepare("
       INSERT INTO `destination_table` (`a`,`b`)
       VALUES(:a, :b1) 
       ON DUPLICATE KEY UPDATE `b` = :b2
      ");
    $stmt->bindParam('a', $a, PDO::PARAM_STR);
    $stmt->bindParam('b1', $b, PDO::PARAM_STR);
    $stmt->bindParam('b2', $b, PDO::PARAM_STR); # set update value if a exists
// Assume we have an associative array of 'a', 'b' values passed to process
    foreach($inputRowToProcess as $arrVals)
    {
      # Set our prepared values from the array elements
        $a = $arrVals['a'];
        $b = $arrVals['b'];
        $stmt->execute();
      /* Now check if something happened and increment $countAnAction
        (
         $stmt->rowCount() on MySQL can be 1 or 2, depending on the action
         based off a value greater than zero, update the row count by 1
        )
      */
        if($stmt->rowCount() > 0)
        {
            $countAnAction += 1;
        }
    }

    $stmt = null;

// Now we get the count of rows in the destination table after the process has finished
    $stmt = $dbx->query("SELECT COUNT(*) FROM `destination_table`");
    $row = $stmt->fetch(PDO::FETCH_NUM);
    $endCount = $row[0];
    $stmt = null;

// Finally apply some math to the various elements to determine inserted count and updated count
                $insertedRows = $endCount - $startCount;
                $updatedRows = ($endCount - $startCount) + $countAnAction;

You are making a very common mistake here, assuming there will be always just a single user for this site. But, obviously, it will be not. and therefore any concurrent process could spoil your math completely. — Your Common Sense, Apr 19 '19 at 07:47
Besides, this assumption of yours, "if the key exists and an update performed, $stmt->rowCount() = 1" is outright wrong. — Your Common Sense, Apr 19 '19 at 08:09
Thanks Your Common Sense, realised the above flaw with the rowCount() assumption and have come back to correct. Also table locking seems to take care of the math problem in my particular 'use case' for added rows. I hadn't added those lines into my work around. Still new to posting here, so is that something I should update as well? — Roy, Apr 19 '19 at 10:23
I don't think it does matter at all, as the goal could be achieved with a simple condition, as it's shown in the linked answer. while table locking is a very dangerous game that should be avoided — Your Common Sense, Apr 19 '19 at 11:52

meda · Answer 2 · 2014-10-28T21:41:56.907

-1

You can cut one step (SELECT) if you use ON DUPLICATE KEY UPDATE. So it will be only one query.

Here is the documentation:

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

As for checking, here is the code:

if($stmt->rowCount() > 0 ){
    if($stmt->lastInsertId() != 0){
        echo 'Row was inserted';
    }else{
        echo 'Row was updated';
    }
}else{
    echo 'row was neither updated or inserted';
}

edited Oct 28 '14 at 21:41

answered Oct 28 '14 at 21:35

meda

45,103
14
92
122

And how do I know wether it actually did insert or update? – Daniel W. Oct 28 '14 at 21:35
@DanFromGermany check for `$stmt->lastInsertId()` if 0 nothing is inserted, for example `($stmt->lastInsertId() > 0)? 'inserted' : 'updated' ` – meda Oct 28 '14 at 21:36
this only works on auto increment keys, it won't work when I have the PK before inserting :/ – Daniel W. Oct 28 '14 at 22:16
What does not work can you please explain – meda Oct 28 '14 at 22:17

score -1 · Answer 3 · answered Oct 28 '14 at 21:36

-1

REPLACE INTO is what you need if you have a PRIMARY KEY or UNIQUE index for your table.

answered Oct 28 '14 at 21:36

Valentin Rodygin

864
5
12

score -1 · Answer 4 · answered Oct 28 '14 at 21:45

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html will do that if your table has a primary key or unique index.

Say you have a table with columns id (primary key), a, and b, and that you have a record (1,2,3). You have new values for id=1, namely a=4 and b=5, but you don't want to check to see if there is already an entry for id=1.


    INSERT 
    (id, a, b)
    VALUES
    (1, 4, 5)
    ON DUPLICATE KEY UPDATE
    a = VALUES(a),
    b = VALUES(b);

will update since the primary key is found.

How to know whether a row was updated or inserted after ON DUPLICATE KEY UPDATE query

4 Answers4