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

[Anonymous Monk]

I don't get it. In an UPDATE statement what is the difference between the rows found and affected. Aren't the rows found by the WHERE clause read first and subsequently those same rows UPDATED too?

[1nickt]

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

[Laurent_R]

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?

[1nickt]

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)

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