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

For completeness, please note that this is a limitation of DBD::mysql, not of MySQL. From the MySQL documentation: 

ROW_COUNT() returns the number of rows changed, deleted, or inserted b
+y the last statement if it was an UPDATE, DELETE, or INSERT.

[download]
For UPDATE statements, the affected-rows value by default is the numbe
+r of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag
+ to mysql_real_connect() when connecting to mysqld, the affected-rows
+ value is the number of rows “found”; that is, matched by the WHERE c
+lause.

[download]
Unfortunately it also says below: 
Important
ROW_COUNT() is not replicated reliably.

[download]
Which I take it is the reason why it doesn't even work to call that function directly (through DBI) as in: 
my $rows = $dbh->selectrow_array( 'select row_count()' );

[download]
. . . which still returns the number of "found" rows (those that match the where clause), not the number of changed rows, as do $sth->rows and the return value of $dbh->do. This is very odd, in my view, since when I run that same SQL in the MySQL CLI, it does return the number of changed rows.

The way forward always starts with a minimal test.

Replies are listed 'Best First'.
Re^2: [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI
by Anonymous Monk on Sep 09, 2015 at 06:15 UTC
    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?
[reply]

      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.
[reply]
        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]

In Section Seekers of Perl Wisdom