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
