CODEWITHSUNDEEP
mysqlcountrecords
yossi
yossi

Reputation: 3164

same query returnd different number of records every time

This query result every time differently:

update product_models
  set category_id = (select id from categories order by RAND() limit 1)

Different counts repeatedly: 884 record(s) were affected
878 record(s) were affected
877 record(s) were affected
892 record(s) were affected
893 record(s) were affected
883 record(s) were affected
885 record(s) were affected

How come?

extra info

in the categories there are 22 rows
in the product models - 900
in the products - 600
the sub query has no conditions, no joins and always return a single row.
the main query also has no conditions or joins, so it should also return a fixed number.

So, every execution should return 900 product models, and each p.m should be updated by the subquery

Upvotes: 0

Views: 326

Answers (3)

Sashi Kant
Sashi Kant

Reputation: 13465

I cant understand why you couldn't understand, since you are using a random function to get the categoryId and every time it returns some other categoryId.

Explanation::

Every time you run this subquery:

 `select id from categories order by RAND() limit 1`

a new Id is generated, lets say we get Category1 at first run, Category2 in second and so on.

So now at every run your update becomes ::

First Run  :: update product_models  set category_id = Category1  
Second Run :: update product_models  set category_id = Category2
Third Run  :: update product_models  set category_id = Category3

Since the number of rows in table product_models are different for different categories, so the number of rows updated differs at every run. Hope you have understood the reason. Feel free to ask if you have any doubt.

Upvotes: 1

rkawano
rkawano

Reputation: 2503

You are sorting categories, and each categorie has different number of products associated. Or table is being altered at time.

Upvotes: 0

G-Nugget
G-Nugget

Reputation: 8846

Assuming that each time that you run the query the data is identical, the cause is that rows aren't counted as affected if the data doesn't change. So if 5 is selected as the random ID, rows that already have 5 for the category_id aren't counted.

Upvotes: 1

Related Questions