I need to populate table with random size of data for each dependency record.
First table is table of products (named products):
id || name === + ===== 1 || name1 2 || name2 3 || name3
And the second table is table that contains displays of that product (named product_displays). Each display is another row:
id_product || date =========== + ==== 1 || d1 1 || d1 1 || d1 1 || d1 2 || d1 2 || d1 3 || d1 3 || d1 3 || d1
Like you can see date will always be the same value, but the number of rows that I need to insert should be random. And range for that number of returned rows I would like to specify in the query.
I except something like this:
INSERT INTO product_displays (id_product, date) SELECT p.id, '2019-07-06' FROM products p JOIN table_with_random_num_of_rows_between_x_and_y t
For each product number of table_with_random_num_of_rows_between_x_and_y rows should be random.
Advertisement
Answer
If I followed you correctly, one simple solution is to join the products table with a table of numbers, using a random number as join condition, like:
insert into product_displays (id_product, pdate)
select p.id, '2019-07-06'
from products p
inner join (
select 1 n union all select 2 union all select 3 union all select 4 union all select 5
) t on t.n <= floor(1 + rand() * 5)
This will produce a random number of records (between 1 and 5) for each product. You can expand the inline table to increase the maximum number of records per product. Alternatively, to manage a great (maximum) number of records, you can use a number table, or use row_number() and an already populated database table with a sufficient number of rows.
Demo on DB Fiddle (you can run the query several times to see the randomness in action):
id_product | pdate
---------: | :---------
1 | 2019-07-06
1 | 2019-07-06
1 | 2019-07-06
1 | 2019-07-06
1 | 2019-07-06
2 | 2019-07-06
2 | 2019-07-06
2 | 2019-07-06
3 | 2019-07-06
3 | 2019-07-06