Re: When UPDATE doesn't work..

[Mike Kruckenberg]

In MySQL there is a function row_count() that will return the number of affected rows on the previous statement, including 0 if nothing was changed:

mysql> select * from person;
+------+-------+
| id | name |
+------+-------+
| 1 | john |
| 2 | frank |
| 3 | harry |
+------+-------+
3 rows in set (0.00 sec)

mysql> update person set name = 'richard' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)

mysql> update person set name = 'frank' where id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)

You can get at that in the DBI using the rows method:

my $sth = $dbh->do("some sql");
my $count = $sth->rows;

Hope that helps.

Mike Kruckenberg
Co-author of Pro MySQL (Apress)
http://mike.kruckenberg.com