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!