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, phone(id,phone,type) and user_phone(id,user_id,phone_id).

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!

  1. Produce the table user_phone as

    insert into phone(phone,type,user_id)
    select phone,type,user_id from userphone
  2. Make your table user_phone as

    insert into user_phone (user_id,phone_id)
    select user_id,id from phone
  3. 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 = and u.type = p.type