How can I check if the values were changed after an update?
Assuming I do something like the following:
my $rows = $dbh->do("UPDATE MYTABLE SET amount=1200 WHERE id =123");
The $rows returns 1 even with the amount is already 1200. So it is considered an updated row. My question is: Is there a way to check if an update actually changed the values in a row besides doing a query before the update?
Change the SQL query to:
UPDATE MYTABLE SET amount=1200 WHERE id = 123 AND amount <> 1200
The table will be identical, but it returns the number of rows that actually changed.
Twinkles answer is correct, but you should create a statement handle using prepare and then execute most of your database queries.
In this case you would write
my $update_if_changed = $dbh->prepare('UPDATE mytable SET amount = ? WHERE id = ? AND amount != ?')
$update_if_changed->execute($amount, $id, $amount)
By default, DBD::mysql returns the number of rows matched in an UPDATE, not the number of rows physically changed. You can change this behavior by disabling mysql_client_found_rows in your call to connect:
my $dsn = "DBI:mysql:;mysql_client_found_rows=0"; my $dbh = DBI->connect($dsn, $user, $password);