Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> At 06:17 PM 9/15/2004, Jochen Wiedmann wrote:
> >Dave Dyer wrote:
> >
> >>The case in point, I'm merging two databases and all the
> >>rows are usually the same.  I'm interested in the exceptions;
> >>and to me "affected rows" means changed rows.  
> >>Maybe it's always been this way, but the C api makes the number
> >>of changed rows easily available, I find it very useful.
> >
> >But you can answer that question by performing a simple count query?
> 
> There are good reasons to just want the count as a side
> effect.  
> 
> For one, it's reliable to say "change this and tell me
> how many changed" it's not reliable to say "if I changed this, how many
> would change" and then say "ok do it" and expect the number of changed
> records to be the same.

They had better be the same ;) : 

eval {
    $dbh->begin_work();
    my $count = $do_count->execute();
    $do_update->execute();  # this better not be updateing a different set
                            # Assuming ANSI default transaction isolation level.
    $dbh->commit();
}; if (my $e = $@) {
    eval {$dbh->rollback()};
    die die die "I am aweary, aweary, /  Oh God, that I were dead! $e";
}

> For another, in the case in point, the query takes several seconds
> and I do thousands of them.  Doubling up would not be especially
> smart.
> 

But you could munge your where clause to only update the rows that need to be 
updated 

And think of it from the point of a record that had a last-updated-time that
*should* probably be changed when you do the update and that would cause
thousands of extra timestamp updates that you don't need, or think of it with 
update triggers in mind they would have to fire, right? So then
you would have a metric boatlaod of extra trigers that could or could not be
updating something.


Rudy


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]