-1

I have a procedure that contains a INSERT ..... ON DUPLICATE KEY UPDATE.

If there a way in MySQL to determine the total rows that were inserted and the total rows that were replaced?

Thanks

Jaylen
  • 39,043
  • 40
  • 128
  • 221
  • possible duplicate of http://stackoverflow.com/questions/10925632/getting-number-of-rows-inserted-for-on-duplicate-key-update-multiple-insert – Gryphius Oct 11 '13 at 14:04

2 Answers2

1

You can use http://dev.mysql.com/doc/refman/5.0/en/mysql-affected-rows.html after the query.

For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value is 1 if the row is inserted as a new row and 2 if an existing row is updated

fasouto
  • 4,386
  • 3
  • 30
  • 66
0

You can calculate this using "Records" and "Duplicates". Records is the total number of rows affected. Duplicates is the number of updates. So Records - Duplicates is the number of inserted rows.

For example:

mysql> insert into your_table (...) 
    -> select ... from your_other_table 
    -> on duplicate key update ...;
Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

That means that 1 new row was inserted and 1 existing row was updated.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • Correct, I need to the functions to extract these results so I can insert the results into a summery database. I need to store how many records are updated and how many are inserted in a different table. so what function return the total records? and what function the return total Duplicate? – Jaylen Oct 11 '13 at 14:40