From: Shawn Green (MySQL)
Date: September 17 2010 1:51am
Subject: Re: Update record count
List-Archive: http://lists.mysql.com/mysql/223015
Message-Id: <4C92C990.3080508@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 9/16/2010 5:12 PM, Jerry Schwartz wrote:
> I should be able to figure this out, but I'm puzzled. Here's a simplified 
> example:
> 
> UPDATE a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
> SET a.f1 = NOW(),
>   b.f2 = NOW()
> WHERE c.f3 IN ('x', 'y', 'z')
> AND b.f4 = 'yen';
> 
> It seems to me that if there are 3 rows found in `c` that match a total of 10 
> rows in `a` that each, in turn, matches 1 row in `b`, then the total number of 
> qualifying would be 10 + 10 -> 20.
> 
> That should also be the number of rows changed.
> 
> Somehow the numbers reported by MySQL don't seem to match up in my real case, 
> even though the results seem to be what I want. The numbers reported were way 
> too high, and I don't understand it.
> 
> I can supply more information, if necessary, but have I gone off the rails 
> somehow?
> 

Look at this like the database sees the problem:

SELECT a.f1, b.f2, c.f3, b.f4
FROM a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';


What you should be able to notice:

* Each matching row from a is combined with each matching row from b

* Each combination of (a,b) rows is combined with each matching row from c

So if 10 rows of A match your conditions, 1 row from B match your 
conditions, and 10 rows from C match your conditions, then this query 
produces 10*1*10 total row combinations.

That should explain why your numbers are higher than expected.

Regards,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN