List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:September 17 2010 1:51am
Subject:Re: Update record count
View as plain text  
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.

Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
Update record countJerry Schwartz16 Sep
  • Re: Update record countMySQL)17 Sep
    • Re: Update record countJohan De Meersman17 Sep
    • RE: Update record countJerry Schwartz17 Sep