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

Perl300 has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

I am using "use DBI" on perl v5.8.8 to update MySQL database table.

Statement I am using is like this:

my $sql6 =  "UPDATE table_name SET good_high = $threshold_good_high, c
+ritical_low = $threshold_critical_low, warning_low = $threshold_warni
+ng_low, warning_high = $threshold_warning_high WHERE user = ? AND nam
+e = '$threshold_name'";
my $sth6 = $dbh->prepare($sql6);
$sth6->execute($userName);
$update_result1 = $sth6->rows;
print "\$update_result1: $update_result1\n";
[download]

Here I am actualy trying to get number of rows changed by this update command (which will vary from 0 to 29 in my case). But rows is always giving me 29 as result even if no row was changed. Which I think is expected as per documentation, but I didn't understand it 100%.

Per documentation of DBI: Returns the number of rows affected by the last row affecting command, or -1 if the number of rows is not known or not available. Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

So, I tried using:

my $sth6 = $dbh->do("UPDATE table_name SET good_high = $threshold_good
+_high, critical_low = $threshold_critical_low, warning_low = $thresho
+ld_warning_low, warning_high = $threshold_warning_high WHERE user = ?
+ AND name = '$threshold_name'");
print "\$sth6: $sth6\n";
[download]

UPDATE(discard):But in the above case it prints nothing as $sth6

UPDATE(consider):But in the above case ALSO it prints 29 as $sth6. I had missed providing $userName earlier.

Can you please suggest me what am I missing here and a way to get count of rows changed by UPDATE command.

Comment on [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI Select or Download Code

Replies are listed 'Best First'.
Re: How to get count of rows changed by MySQL UPDATE command using DBI by runrig (Abbot) on Sep 08, 2015 at 23:55 UTC
`my $rows_affected = $sth->execute(); # Or my $rows_affected = $dbh->do("update table....");` [download] Update: Oh, well, that will still just give you the number of rows "matched" by any where clause in the update statement, or the total number of rows in the table if there is no where clause...there is no way to easily get only the number of rows "changed", because the database "updates" the data whether the values change or not. You would first have to save the current values, then compare against the values after the update...	[reply] [d/l]
Re^2: How to get count of rows changed by MySQL UPDATE command using DBI (where) by tye (Sage) on Sep 09, 2015 at 00:07 UTC
Or add every updated column to the WHERE clause. `UPDATE table_name SET good_high = $tgh , critical_low = $tcl , warning_low = $twl , warning_high = $twh WHERE user = ? AND name = '$threshold_name' AND good_high <> $tgh AND critical_low <> $tcl AND warning_low <> $twl AND warning_high <> $twh` [download] Oops: Built that WHERE clause incorrectly. It should have been: `UPDATE table_name SET good_high = $tgh , critical_low = $tcl , warning_low = $twl , warning_high = $twh WHERE user = ? AND name = '$threshold_name' AND ( good_high <> $tgh OR critical_low <> $tcl OR warning_low <> $twl OR warning_high <> $twh )` [download] - tye	[reply] [d/l] [select]
Re: [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI by 1nickt (Canon) on Sep 09, 2015 at 05:33 UTC
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.	[reply] [d/l] [select]
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]
Re^3: [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI by 1nickt (Canon) on Sep 09, 2015 at 06:57 UTC
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]
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
Re^5: [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI by 1nickt (Canon) on Sep 09, 2015 at 13:58 UTC
Some notes below your chosen depth have not been shown here
Re: How to get count of rows changed by MySQL UPDATE command using DBI by Perl300 (Friar) on Sep 09, 2015 at 03:27 UTC
Thank you runrig and tye for your responses. tye, your suggestion worked for me as I updated the query and the used rows on it to get the count which this time was actual rows that were changed. I think this improved performance of my query as well. Thanks again. Updating subject as solved.	[reply]
Re: [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI by chacham (Prior) on Sep 09, 2015 at 13:41 UTC
Side comment: The UPDATE is using dynamic SQL. Interesting as there is a placeholder as well. You might want to change all the other variables to placeholders and pass the variables in the execute() as well, which would make the statement more secure.	[reply]
Re: [Solved]: How to get count of rows changed by MySQL UPDATE command using DBI by locked_user sundialsvc4 (Abbot) on Sep 09, 2015 at 14:53 UTC
The admonition, “or -1 if the count is unavailable,” should be considered very soberly: quite a few RDBMSes do not provide a count, even of potentially-affected lines. Programming which assumes the existence of a useful return value in this scenario will not be transportable to other RDBMS platforms ... a very nasty application-migration “gotcha” that can blow a project-plan to hell. The only truly reliable way that I know of to produce the count is to, within a `TRANSACTION` with a proper isolation level (and, in the case of MySQL, assuming a table-type that supports them), first `SELECT` to find out how many rows there are, then `UPDATE` to affect them, then `COMMIT`. And this “extra select-query” is fairly expensive for the fairly meager amount of information that it produces, especially if the table structure is such that the query is not efficient.