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) [mailto:shawn.l.green@stripped] >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 >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

Thread | ||
---|---|---|

• Update record count | Jerry Schwartz | 16 Sep |

• Re: Update record count | MySQL) | 17 Sep |

• Re: Update record count | Johan De Meersman | 17 Sep |

• RE: Update record count | Jerry Schwartz | 17 Sep |