CODEWITHSUNDEEP
mysqlsqlsql-update
yossi
yossi

Reputation: 3164

Updating random records in 2 tables in a single query

Given this tables structure, 

categories{id}
product_models{id, category_id}
products{product_model_id, category_id}

Which is already populated, but with product_models.category_id, products.category_id and products.product_model_id all set to NULL, I need a single query that will "connect" them all, doing the follow:

  1. set all product_models.category_id, to a random value from the categories table
  2. set all products.product_model_id, to a random value from product_models table
  3. set each products.category_id, to the value of the category_id of the newly assigned product_models record.

Can it be done in a SINGLE query?

Upvotes: 0

Views: 81

Answers (2)

Ankit
Ankit

Reputation: 690

If i am able to understand you requirement, this is what you require

 Create Procedure usp_insert
 as
 begin
    declare @rand1 int
    declare @rand2 int
    set @rand1=rand()*10000
    set @rand2=rand()*10000

    insert into categories (id) values (@rand1)
    insert into product_models{id, category_id} values (@rand2,@rand1)
    insert into products{product_model_id, category_id} values (@rand2,@rand1)
End

above block will create a procedure in your database

to execute the procedure use following code

exec usp_insert

Each execution of the procedure will insert one row in each of the tables e.g. suppose random numbers generated are 3423,2345 then it will 1. insert a row in categories table with 3423 as id 2. insert a row in product_models table with 3423 as category_id and 2345 as id 3. insert a row in product_models table with 3423 as category_id and 2345 as product_model_id

you can adjust the insert queries according to your requirement.

Upvotes: 1

Dale M
Dale M

Reputation: 2473

No

The RAND function only executes once in any query and is effectively 'locked' to a single value no matter how many times it is used.

Upvotes: 1

Related Questions