I have 3 Tables. model, category, and document. Documents belong in a category, which belongs to a model, and models can have multiple categories, which can have multiple documents.
I'm trying to build & execute this query in PHP, assigning each document a randomly selected category from a list of the categories in the currently selected model. So, if a model has 10 categories (cat1-cat10), with each of those categories having 10 documents, the end result would make 1000 documents have a random_category_id field of cat1 - cat10 assigned at random, but not overwriting the existing category_id of the document.
Later in the application, I need to be able to calculate when document.category_id == document.random_category_id.
Is there a way to do this in one query. I'm new to SQL & PHP (and haven't mastered any kind of JOIN yet), so please forgive the blunders in database design & mixed coding approaches. I know the below example will not execute.
I'm using MySQL 5.5.28 with InnoDB.
Pseudocode Example
$catList = SELECT category_id FROM category WHERE model_id = '$current_model_id'
UPDATE document.random_category_id = RANDOM($catList) WHERE document.model_id = '$current_model_id'
Thank you!