CODEWITHSUNDEEP
mysqlsql
Hrvoje Brkasic
Hrvoje Brkasic

Reputation: 3

MySQL generate random number that depends on column value

I've been searching for few hours and I have no idea how to do this. The problem is next. I have two tables, customers and orders. Customers have id and 5-6 columns that are empty and have to be filled with random data. For this purpose, lets take orders_accepted_campaigns as column that we want to fill with data. In orders table we have customer_ids and order values. So how should I write a query that will fill each this orders_accepted_campaigns field with random number that is lower than number of orders that each customer has made (count(customer_id) from orders group by customer_id) so you can't have 4 orders and 5 orders_accepted_campaigns?

Upvotes: 0

Views: 255

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

It sounds like you want an update:

update customers c left join
       (select customer_id, count(*) as cnt
        from orders o
        group by customer_id
       ) o
       on o.customer_id = c.customer_id
    set orders_accepted_campaigns = floor(rand() * cnt);

Note: This sets the value to 0 when the customer has zero orders.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108676

If you want an uniformly distributed random integer between 0 and k-1 inclusive, use this expression.

  TRUNCATE(RAND() * k , 0)

Avoid CAST; it rounds rather than truncates, which messes up the uniform distribution.

Upvotes: 2

Related Questions