Getting the number of insert / update rows from ON DUPLICATE KEY UPDATE

Question

I have an instruction that tries to insert a record, and if it already exists, it just 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 several attachments, and I expect to count the number of INSERT versus UPDATE. Can I do this using MySQL variables, I have not yet found a way to do this after searching.

Answer 1

From Mysql Documents

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

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

Answer 2

I know this is a bit outdated, but I did a big insert in PHP and I 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 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 | ------------------------------- 

Answer 3

if you want to get the number of records that were inserted and updated separately, you must issue each statement separately.

Answer 4

You should use mysql_affected_rows() right after your request.

http://dev.mysql.com/doc/refman/5.1/en/mysql-affected-rows.html

Answer 5

I did what you describe using a while loop so that each iteration creates a MySQL statement that affects one row. Inside the loop, I run mysql_affected_rows () and then increment the counter depending on whether the return value was 0 or 1. At the end of the loop, I repeat both variables for viewing.

The full wording of the MySQL Docs regarding the mysql_affected_rows function (note that 3 possible values ​​are returned - 0, 1, or 2):

For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected row value in the row is 1 if the row is inserted as a new row, 2 if the existing row is updated, and 0 if the 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 the existing row is set to its current values.

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

Here is the code 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 didn’t make any typos as I recreated them for sharing, deleting my sensitive data.

Hope this helps someone. Good luck codes!