0

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?

yossi
  • 3,090
  • 7
  • 45
  • 65

2 Answers2

1

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.

Dale M
  • 2,453
  • 1
  • 13
  • 21
1

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.

Ankit
  • 680
  • 4
  • 17
  • great! seems like it's the solution, but, can't it be done with one pure sql query? using joins? – yossi Feb 06 '13 at 15:37
  • No, there is no way of doing this in a single step. You will need to perform multiple queries, and why do we need such query? it works fine without any problem. Also, we have converted it to a stored procedure, its as good as a single query for you. – Ankit Feb 06 '13 at 16:28
  • I referred this link before putting above comment http://stackoverflow.com/questions/14700810/using-pdo-to-insert-multiple-records-in-multiple-tables-from-one-statement – Ankit Feb 06 '13 at 16:33