Update record count

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?


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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.

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=gcdmg-mysql-2@m.gmane.org

Re: Update record count

--001636cd730a5a397a049072b096
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Sep 17, 2010 at 3:51 AM, Shawn Green (MySQL) <
shawn.l.green@oracle.com> wrote:

>
> 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.
>

Umm. It's friday, so I may be rather off it, too, but aren't the conditions
inclusive ? They're AND, not OR. A Cartesian join on a(10 rows), b(1 row)
and c(10 rows) would produce as you say.

I would say that the number of rows is the number where
* c.f3 is x, y or z
* AND where b.f4 = yen
* FROM the set produced by the join condition.


Check the number of rows in c that match f3 in (x, y, z).
Match those with rows in b as per join. This will yield the same or less
rows.
Filter those that match b.f4 = yen. This again will yield same or less.
Match those with a as per join. This, too, will yield same or less, and will
be your final number of rows.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001636cd730a5a397a049072b096--

RE: Update record count

>-----Original Message-----
>From: Shawn Green (MySQL) [mailto:shawn.l.green@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=gcdmg-mysql-2@m.gmane.org