Re: How to get count of rows changed by MySQL UPDATE command using DBI


P is for Practical
	PerlMonks

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 ( [id://1141382]=note: print w/replies, xml )

Need Help??

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

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...

Comment on Re: How to get count of rows changed by MySQL UPDATE command using DBI Download Code

Replies are listed 'Best First'.

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]

In Section Seekers of Perl Wisdom