CODEWITHSUNDEEP
mysqlsqlrelationship
yossi
yossi

Reputation: 3164

connecting records in 3 tables randomly

I have three tables: 

categories (id)
product_models(id, category_id)
products(id, product_id, category_id)

There are records on each table (lorem ipsum content), i want to connect the data.
I've changed already the product_models, executing this query: 

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

How can I use a single query that will map all the products to a product model and update the category also?

Upvotes: 0

Views: 62

Answers (2)

Muhammad Hani
Muhammad Hani

Reputation: 8664

Assuming that your third table structure is (id, product_model_id, category_id), another assumption is that you have a composite foreign key (product_model_id, category_id), then you have to update the third table like below

UPDATE products P
INNER JOIN (SELECT id, category_id from product_models ) M
ON P.product_id = M.id
SET P.category_id = M.category_id

Upvotes: 1

Eric Hotinger
Eric Hotinger

Reputation: 9306

You should add constraints and references to the tables as appropriate so that when you update one table, the others will be updated as well.

Upvotes: 1

Related Questions