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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org

Reply via email to