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?

Answers


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

and subsequently

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

Need Your Help

Implementing 'show in finder' button in Objective C

objective-c macos cocoa finder

In my application I would like to create a 'show in finder' button. I have been able to figure out how to pop up a finder window of that directory but haven't figured out how to highlight the file ...

How to get Click Event of QLineEdit in Qt?

qt

How to get Click Event of QLineEdit in Qt ?