At this time I've got a mysql database having a table
userphone(id, phone, type,userid) and Let me split it into two tables,
I am carrying this out because I am likely to produce other stuff that also make reference to telephone numbers, and they should be all saved in a single table. (e.g. I'll be also adding a
company_phone(id,company_id,phone_id) join table to become listed on companies using their telephone numbers).
Can there be worthwhile, efficient method of carrying this out? I have added the brand new tables, but am unsure of the greatest method for inhabiting them.
I'm able to add all of the phones from
userphone in to the
phone table with something similar to
INSERT INTO phone(phone,type) SELECT phone,type FROM userphone;
but that does not add an entry in to the new
user_phone table for every phone.
Can there be in whatever way to
INSERT INTO two different tables at the same time? (from research, I understand there is not, however i can't work out how else to do this...)
Thanks ahead of time!
Produce the table user_phone as
insert into phone(phone,type,user_id) select phone,type,user_id from userphone
Make your table user_phone as
insert into user_phone (user_id,phone_id) select user_id,id from phone
alter table phone drop column user_id
Place INTO phone(phone,type) Choose phone,type FROM userphone
choose u.USER_ID, p.phone_ID from userPhone u INNER JOIN Phone p ON u.phone = p.phone and u.type = p.type