List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:September 17 2010 6:57pm
Subject:RE: Update record count
View as plain text  
>-----Original Message-----
>From: Shawn Green (MySQL) []
>Sent: Thursday, September 16, 2010 9:51 PM
>To: Jerry Schwartz
>Cc: mysql@stripped
>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
>> 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 
- `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 

- 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 

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.

>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