How to get the number of rows affected by INSERT ON DUPLICATE using PDO?

Question

Using PHP's PDO with a MySQL database, how do I get the number rows affected by an INSERT ... ON DUPLICATE KEY UPDATE ... statement?

For instance, how would it be done with the following query?

INSERT INTO table1(id,a,b,c)
SELECT id,1,2,3 FROM table2 WHERE x=321
ON DUPLICATE KEY UPDATE a=1,b=2,c=3;

The value returned by PDO's `rowCount()` will be 1 for an insert or 2 for an update to an existing row. — Michael Berkowski, Apr 16 '16 at 17:27
@MichaelBerkowski Yes, I know that MySQL does as you stated per http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html, but that does not answer the question. — user1032531, Apr 16 '16 at 17:29
What is your code so far, what is expected, what is actually the result? — Pinke Helga, Apr 16 '16 at 17:31
@user1032531 UNIQUE keys reference exactly one row. A single insert will never modify more than one. Are you talking about using multiple inserts via `VALUES (...), (...), (...)`? — Michael Berkowski, Apr 16 '16 at 17:33
The desired results are the number of times a row is either inserted or updated. The actual results are one more "rows affected" for every time a row has a duplicate and is updated. — user1032531, Apr 16 '16 at 17:35
See also http://stackoverflow.com/questions/10925632/getting-number-of-rows-inserted-for-on-duplicate-key-update-multiple-insert — Michael Berkowski, Apr 16 '16 at 17:36
@MichaelBerkowski Your thread from your latest comment seems to be right on, however, still not sure how it will be actually accomplished. I will first need to obtain `numberOfValuesInInsert` which I don't know is possible. — user1032531, Apr 16 '16 at 17:43
Isn't a multiple executed prepared statement with single row `INSERT` an option? — Pinke Helga, Apr 16 '16 at 18:15
@user1032531 It would help if you posted an example of the insert statement you're doing. If you did `INSERT INTO (...) VALUES (...),(...),(...),(...)` then `numberOfvaluesInInsert` is 4, since 4 sets were passed to `VALUES`. If you are dynamically building that statement from an array, then the array length is `numberOfValuesInInsert`. — Michael Berkowski, Apr 16 '16 at 19:21
If you really must have an accurate number, you probably need two queries. You can run the inserts separately in a transaction: http://stackoverflow.com/a/4920619/1670021, and then run the updates to see how many change (add the two numbers for a total.) Or use triggers as described by @Quasimodo'sclone. — Todd Christensen, Apr 16 '16 at 20:48
Do you mean counts of inserted vs updated? imo, then triggers are probably useful? Are you using auditing of changes currently? If so then could you use that information? — Ryan Vincent, Apr 16 '16 at 20:48
@RyanVincent auditing could be of general interest to people looking for similar questions. You should provide further description in an answer. — Pinke Helga, Apr 16 '16 at 21:05
Oh, you're using `INSERT INTO... SELECT...`. Is the select `table2` static, or is it likely to be updated often? I don't know of a way to determine how many would be inserted from that except to issue a `COUNT()` query against it first with the same `WHERE`, but that may require locking to prevent changes before the `INSERT` is done. — Michael Berkowski, Apr 17 '16 at 13:19

Pinke Helga · Answer 1 · 2016-04-16T20:41:03.907

If you get the (possibly generated) SQL code, you could examine the number of inserts to be done by parsing the statement with a regular expression.

Another approach is to define a trigger. In this case you have to set a user defined variable before the execution of the statement and read it via another query after the execution.

CREATE TRIGGER `beforeUpdateTABLENAME`
  BEFORE UPDATE ON `tablename` FOR EACH ROW
  SET @updateRowCount = @updateRowCount + 1;

Reset row count

$connection->exec('SET @updateRowCount = 0;');

Execute the INSERT ... ON DUPLICATE KEY UPDATE ... and read the number of updates

$result = $conn->query('SELECT @updateRowCount;');

Calculate rows affected = row count - updated rows

If you do not want to distinguish between inserts and updates at all, you can trigger on INSERT as well and no calculation is needed.

CREATE TRIGGER beforeInsertTABLENAME
  BEFORE INSERT ON `tablename` FOR EACH ROW
  SET @rowsAffectedCount = @rowsAffectedCount + 1;

How to get the number of rows affected by INSERT ON DUPLICATE using PDO?

1 Answers1