Getting count of insert/update rows from ON DUPLICATE KEY UPDATE

Question

I have a statement that tries to insert a record and if it already exists, it simply updates the record.

INSERT INTO temptable (col1,col2,col3)
VALUES (1,2,3)
ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), col3=VALUES(col3);

The full statement has multiple inserts and I'm looking to count number of INSERTs against the UPDATEs. Can I do this with MySQL variables, I've yet to find a way to do this after searching.

I don't think your example query will actually update anything, only insert as there doesn't seem to be a primary key that you aren't updating in the `ON DUPLICATE KEY` clause. It basically becomes a simple INSERT if you do it like it is in the example. — Steph Locke, Jun 13 '13 at 12:17
I stripped down the code for the example, but the full statement will either insert or update. I m just wondering how I can count the inserts and updates to display to the user — Shaun Perry, Jun 13 '13 at 12:19
@ShaunPerry Is the statement will be 1 or it will have many insert query also. — Yogesh Suthar, Jun 13 '13 at 12:20
Ok so I set a trigger on the INSERT and a transaction around it. Do you know of any good sites with examples, I've never used triggers before. — Shaun Perry, Jun 13 '13 at 12:26

Yogesh Suthar · Accepted Answer · 2013-06-13T12:32:54.467

8

From Mysql Docs

In the case of "INSERT ... ON DUPLICATE KEY UPDATE" queries, the return value will be 1 if an insert was performed, or 2 for an update of an existing row.

Use mysql_affected_rows() after your query, if INSERT was performed it will give you 1 and if UPDATE was performed it will give you 2.

edited Jun 13 '13 at 12:32

answered Jun 13 '13 at 12:26

Yogesh Suthar

30,424
18
72
100

2

Provided the record is not an exact duplicate: `the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values` – Justin E Dec 14 '16 at 19:10
10

But What happens when you insert 2 or more records at the same time, say one that ends up being inserted and another that ends up being updated??? – gunslingor Feb 01 '18 at 14:22
@gunslingor you get affected rows=3 – Max Segal Aug 19 '22 at 14:13

Keefer · Answer 2 · 2016-05-17T15:54:11.460

I've accomplished what you're describing using a while loop so that each iteration creates a MySQL statement that affects one row. Within the loop, I run the mysql_affected_rows() and then increment a counter depending upon whether the value returned was a 0 or a 1. At the end of the loop, I echo both variables for viewing.

The complete wording from MySQL Docs regarding the mysql_affected_rows function is (notice there are 3 possible values returned - 0, 1, or 2):

For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

(Sidenote - I set $countUpdate and $countInsert and $countUpdateNoChange to 0 prior to the while loop):

Here's the code that I developed that works great for me:

while (conditions...) {    
$sql = "INSERT INTO test_table (control_number, name) VALUES ('123', 'Bob')
    ON DUPLICATE KEY UPDATE name = 'Bob'";  

    mysql_query($sql) OR die('Error: '. mysql_error()); 

    $recordModType = mysql_affected_rows();

    if ($recordModType == 0) {
         $countUpdateNoChange++;
    }elseif($recordModType == 1){
         $countInsert++;
    }elseif($recordModType == 2){
         $countUpdate++;
    };
};

echo $countInsert." rows inserted<br>";
echo $countUpdateNoChange." rows updated but no data affected<br>";
echo $countUpdate." rows updated with new data<br><br>";

Hopefully, I haven't made any typos as I've recreated it to share while removing my confidential data.

Hope this helps someone. Good luck coding!

score 2 · Answer 3 · answered Sep 16 '15 at 14:02

I know this is a bit old, but I was doing a bulk insert in PHP and needed to know exactly how many rows were inserted and updated (separately).

So I used this:

$dataCount = count($arrData); // number of rows in the statement
$affected  = mysql_affected_rows(); // mysqli_*, PDO's rowCount() or anything

$updated  = $affected - $dataCount;
$inserted = 2 * $dataCount - $affected;

Simple trace table:

-------------------------------
| data | affected | ins | upd |
-------------------------------
|    1 |        1 |   1 |   0 |
-------------------------------
|    2 |        2 |   2 |   0 |
|    2 |        3 |   1 |   1 |
|    2 |        4 |   0 |   2 |
-------------------------------
|    3 |        3 |   3 |   0 |
|    3 |        4 |   2 |   1 |
|    3 |        5 |   1 |   2 |
|    3 |        6 |   0 |   3 |
-------------------------------
|    4 |        4 |   4 |   0 |
|    4 |        5 |   3 |   1 |
|    4 |        6 |   2 |   2 |
|    4 |        7 |   1 |   3 |
|    4 |        8 |   0 |   4 |
-------------------------------
|    5 |        5 |   5 |   0 |
|    5 |        6 |   4 |   1 |
|    5 |        7 |   3 |   2 |
|    5 |        8 |   2 |   3 |
|    5 |        9 |   1 |   4 |
|    5 |       10 |   0 |   5 |
-------------------------------

What? I don't get it. If data is, say 3 and affected returns as 3. How do you know if its 1 ins + 1 upd or 3 ins + 0 upd? Both of those would result in 3... — Clox, Jan 13 '17 at 16:06
Hi there! Look at the table that I posted. If you're inserting 3 rows and `mysql_affected_rows()` returned 3, it wouldn't be 1 ins and 1 upd, because that's only 2 rows, right? :D — vcampitelli, Jan 16 '17 at 12:58
I think this will only work if you know that you don't have any rows where the row is updated to the same values that were already there, because MySQL will return a 0 for those rows, which breaks the assumptions made here. Depending on your needs, the CLIENT_FOUND_ROWS flag may help: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html — cincodenada, Jan 11 '22 at 21:21

score 0 · Answer 4 · answered Jun 13 '13 at 12:18

0

if you want to get the number of records that have been inserted and updated separetly, you are to issue each statement separetly.

answered Jun 13 '13 at 12:18

dursun

1,861
2
21
38

1

elaborate it please what you are trying to say – M Khalid Junaid Jun 13 '13 at 12:19
1

ok a better example is: INSERT INTO temptable (col1,col2,col3) VALUES (1,2,3), (3,2,1),(4,5,6) ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), col3=VALUES(col3); – Shaun Perry Jun 13 '13 at 12:42

Getting count of insert/update rows from ON DUPLICATE KEY UPDATE

4 Answers4

Linked

Related