I'm using an INSERT .. SELECT to 'duplicate' rows from a table (with a different foreign key).
This works fine, however it's inserting multiple rows and I now need to do another INSERT to insert multiple rows that are linked to each individual row in the previous SELECT... does that even make sense? Haha.
Basically there's a one to many relationship between table1 and table2. There's a one to one relationship between table1 and table3. I'm 'assigning' rows from table1(and their relevant linked table2 rows) to a new row on table3 (i have the mysql_insert_id for this one). I'm trying to do it with as few queries as possible.
I'll give an example,
You have a products table an options table and an option_items table. Each option has multiple option_items and each product can have multiple options applied to it.
Product1 has the option Colour applied to it, and Colour has the items Blue and Green, Product1 also has the Size option applied to it which has the items Small and Large.
I wish to duplicate the product with a new ID, which means duplicating the rows in both the options and option_items tables and re-creating their relating foreign keys.
I've created the new product and have the productid.
Now I need to duplicate the options get the id of the new option, duplicate the items and assign the ID of the newly created option to the items (maintaining the option to item relationships).
Any help would be muchly appreciated!
Sorry for the crap question! I ended up just using a loop instead of complicated queries. Probably not the best solution, but by far the most straight forward :)
Thanks for reading guys.