CODEWITHSUNDEEP
mysqlsqlperlsql-update
Jim
Jim

Reputation: 19582

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?

Upvotes: 11

Views: 3171

Answers (3)

ThisSuitIsBlackNot
ThisSuitIsBlackNot

Reputation: 24083

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);

Upvotes: 2

Borodin
Borodin

Reputation: 126762

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 != ?')

and subsequently

$update_if_changed->execute($amount, $id, $amount)

Upvotes: 1

Twinkles
Twinkles

Reputation: 1994

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.

Upvotes: 12

Related Questions