>-----Original Message-----
>From: Shawn Green (MySQL) [mailto:shawn.l.gr...@oracle.com]
>Sent: Thursday, September 16, 2010 9:51 PM
>To: Jerry Schwartz
>Cc: mysql@lists.mysql.com
>Subject: Re: Update record count
>
>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.
>
[JS] Thanks --- I think. I realize that left out some crucial details, and
also made a mistake when posing my example. Let me use some more suggestive
field names.
UPDATE `prod` JOIN `prod_price` ON `prod`.`prod_id` = `prod_price`.`prod_id`
JOIN `pub` ON `prod`.`pub_id` = `pub`.`pub_id`
SET `prod`.`prod_changed` = 1,
`prod_price`.`prod_price_tax` = .7
WHERE `pub`.`pub_id `IN ('x', 'y', 'z')
AND `prod_price`.`prod_price_curr` = 'yen';
- `prod`.`prod_id` is a unique key for `prod`, but is not unique in
`prod_price`
- `pub`.`pub_id` is a unique key for `pub`, but is not unique in `prod`
Assume that
- For `pub`.`pub_id` = 'x' we match 3 rows in `prod`:
* `prod`.`prod_id` = 'a'
* `prod`.`prod_id` = 'b'
* `prod`.`prod_id` = 'c'
- For `pub`.`pub_id` = 'y' we match 5 rows in `prod`:
* `prod`.`prod_id` = 'm'
* `prod`.`prod_id` = 'n'
* `prod`.`prod_id` = 'o'
- For `pub`.`pub_id` = 'z' we match 2 rows in `prod`:
* `prod`.`prod_id` = 'q'
* `prod`.`prod_id` = 'r
That should give us a total of 10 rows for this part of our search. Now assume
that
- For each value of `prod`.`prod_id` there are 2 matching rows in
`prod_price`, BUT for each value of `prod`.`prod_price` only one row matches
the WHERE condition `prod_price`.`prod_price_curr` = 'yen'. For that reason we
have a 1:1 relationship between the rows in `prod` and the remaining rows from
`prod_price`.
So are you saying that the count of rows selected would be
3 x 10 x 10
even though the actual number of rows we're working on is only 10 (10 rows
from `prod` and 1 row from `prod_price` for each row from `prod`)?
I know that when it reports the number of rows affected, it adds up the
numbers from each table; but a SELECT based upon the criteria used in my
UPDATE reports the number of records I would expect.
>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
