Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> At 05:00 PM 9/15/2004, Jochen Wiedmann wrote:
> >Dave Dyer wrote:
> >
> >>For some UPDATE queries (possibly those involving joins?), the number of
> >>rows reported for the query is the number of rows processed, rather than the
> >>number of rows actually changed.
> >
> >What does the distinction between "affected" and "matched" mean? Is it, that some rows already had the new value? If so, I would think that I would indeed want to see the value "matched", because the typical use case is I can see is comparing the number of rows against 0.
> 
> 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.  
> 

You can get that by toggling mysql_clinet_found_rows, but it would be 
non-portable.

> Maybe it's always been this way, but the C api makes the number
> of changed rows easily available, I find it very useful.
> 

IMO, that is just the C api exposing something that it maybe should not be
exposing to the user viz., an internal optimization that the database does to
cut down on disk operations. The fact is, the update operation *would/should*
update the whole set (and in an abstract sort of way it can be viewed as having
updated the whole set), but mysql notices that it does not need to physically
change the rows on disk because they are already the same and therefore returns
early and tells you (for some reason).


What you are asking the db to do is:

  UPDATE thetable
  SET thecol = $new_value 
  WHERE thecol <> new_value AND thecol = $some_old_value;

Of course if you have this you already know whether or not you need to do the 
update, so maybe the folowing example is more applicable, though not as clear:

  UPDATE thetable
  SET thecol = $new_value
  WHERE thecol <> new_value

both of which will only update thecol when the_col is != to new_value.

And Tim seems to want it to work this way too:

http://www.mail-archive.com/[email protected]/msg02136.html

Injoy,

Rudy



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