0

(MySQL) I am trying to migrate a "subscription" table into 3 new tables: "product", "subscription", "actual" where "actual" is the name of the actual product, say, newsletter. The subscription has a FK reference to product and the actual has FK reference into subscription.

I know the INSERT INTO SELECT statement can copy data probably from many table to one; is there a statement to do the opposite, one to many tables for my case?

ajreal
  • 46,720
  • 11
  • 89
  • 119
Jake
  • 11,273
  • 21
  • 90
  • 147

2 Answers2

0

I think you can use three seperate insert into select statements. First you convert the product table, then the subscription where you can use an embedded select to find the id in the product table:

insert into subscription (some_column, FK_id,...)
select something, (select id from product where <your where clause>),...

and finally convert the actual table using an embedded select to get the id from the subscription table.

Blackcom
  • 146
  • 6
  • thanks blackcom, I don't think it is possible to get the product.id with a WHERE clause because other than the PK, the other fields are non-unique. Is there an SQL for-loop to do 3 inserts per old subscription record? – Jake Dec 13 '10 at 15:57
0

I'm not aware of an SQL statement that will do what you want. Just do several INSERT INTO SELECTs one after the other. It may be faster to do them one at a time anyway.

  • thanks blackcom, I don't think it is possible to get the product.id with a WHERE clause because other than the PK, the other fields are non-unique. Is there an SQL for-loop to do 3 inserts per old subscription record? – Jake Dec 13 '10 at 16:05