in reply to Re^2: [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI
in thread [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI

They're only updated if the value in the update query is different from the current value.

The way forward always starts with a minimal test.
  • Comment on Re^3: [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI

Replies are listed 'Best First'.
Re^4: [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI
by Laurent_R (Canon) on Sep 09, 2015 at 07:59 UTC
    I do not have a MySQL install available right now to test, but I would take it that, in an update query, most (if not all) relational DB engines will report the number of raws matched by the where clauses, whether or not the new values are equal or not to the old ones. In effect, AFAICT, for the DB engine, the raws are being updated even if the new values happen per chance to be the same as the old ones. I would therefore think that the only way to know the number of raws where some value actually changed is to add where clauses as suggested by tye.

    Or am I wrong on that?

[reply]

      Salut Laurent_R,

      Well, the thread is about MySQL, and I cannot speak about other RDBMS. But I did quote the documentation. Here's an example: 

      mysql> select bar, baz from foo;
+------+-------+
| bar  | baz   |
+------+-------+
| a    | qux   |
| b    | qux   |
| c    | qux   |
| d    | other |
+------+-------+
4 rows in set (0.00 sec)

mysql> update foo set bar = 'b' where baz = 'qux';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

      [download]
      But as we are discussing, you can't apparently get row_count() to return the changed rows with DBD::mysql.

      The way forward always starts with a minimal test.
[reply]
[d/l]
[select]
        Alright, 1nickt, thank you ++, I am a bit surprised that the DB would even care, but, yes, your demonstration is very clear.
[reply]

In Section Seekers of Perl Wisdom