CODEWITHSUNDEEP
mysqlsqlsql-insert
miitchel
miitchel

Reputation: 405

How can I easily INSERT data in a table from multiple columns from another table?

I want to take all phone numbers from the companies table, and put that in a dedicated phone numbers table, is there an easy way to do this using (if possible) only one query?

example data from the companies table (tel3 and tel4 could have phone numbers):

id tel tel2 tel3 tel4
1 32772373636 32724522341
2 32783675626
3 32968381949

expected example output in phonenrs table:

id company_id phonenr
1 1 32772373636
2 1 32724522341
3 2 32783675626
4 3 32968381949

Upvotes: 1

Views: 68

Answers (2)

Mureinik
Mureinik

Reputation: 312086

You could use an insert-select statement from a query that union alls the phone numbers:

INSERT INTO numbers (company_id, phonenr)
SELECT id, tel FROM numbers WHERE tel IS NOT NULL
UNION ALL
SELECT id, tel2 FROM numbers WHERE tel2 IS NOT NULL
UNION ALL
SELECT id, tel3 FROM numbers WHERE tel3 IS NOT NULL
UNION ALL
SELECT id, tel4 FROM numbers WHERE tel4 IS NOT NULL

Upvotes: 1

blabla_bingo
blabla_bingo

Reputation: 2162

To get the EXACT match to your intended output, first we need to add a row id for your new id column. Then,to make sure the sorting precedence of tel > tel2 > tel3 > tel4, we can perform a trick to do so. Here is the code written and tested in workbench:

select @row_id:=@row_id+1 as id, id as company_id,trim(leading '0' from phone ) as phonenr
from
(select id,ifnull(concat('000',tel),1) as phone from companies
union all
select id,ifnull(concat('00',tel2),1) from companies
union all
select id,ifnull(concat('0',tel3),1) from companies
union all
select id,ifnull(tel4,1) from companies
) t1, 
(select @row_id:=0) t2
where phone !=1
order by company_id,phone
;
-- result:
# id, company_id, phonenr
1, 1, 32772373636
2, 1, 32724522341
3, 2, 32783675626
4, 3, 32968381949

As you can see, by adding different number of leading zero to the phone,we can manipulate the sorting precedence. Without it, I got 32724522341 instead of 32772373636 for the first line.

Upvotes: 0

Related Questions