CODEWITHSUNDEEP
mysqldatabaseinsert-into
Tara
Tara

Reputation: 615

Splitting a table into two join tables

Right now I have a mysql database with a table userphone(id, phone, type,userid) and I'd like to split it into two tables, phone(id,phone,type) and user_phone(id,user_id,phone_id).

I'm doing this because I'm going to have other things that also refer to phone numbers, and they ought to be all stored in one table. (e.g. I'll also be adding a company_phone(id,company_id,phone_id) join table to join companies with their phone numbers).

Is there any good, efficient way of doing this? I've added the new tables, but am not sure of the best approach for populating them.

I can add all the phones from userphone into the phone table with something like

INSERT INTO phone(phone,type) SELECT phone,type FROM userphone;

but that doesn't add an entry into the new user_phone table for each phone.

Is there any way to INSERT INTO two different tables at once? (from research, I know there isn't, but I can't figure out how else to accomplish this...)

Thanks in advance!

Upvotes: 1

Views: 171

Answers (2)

Icarus
Icarus

Reputation: 63964

  1. Create the table user_phone as

    insert into phone(phone,type,user_id)
select phone,type,user_id from userphone

  2. Create 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

Upvotes: 2

AJP
AJP

Reputation: 2125

first:

INSERT INTO phone(phone,type) SELECT phone,type FROM userphone;

second:

select u.USER_ID, p.phone_ID from userPhone u INNER JOIN Phone p ON u.phone = p.phone and u.type = p.type

Upvotes: 0

Related Questions