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

miitchel 0
Viewed 27

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
sql-insert mysql sql
2 Answers

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

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

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.

blabla_bingo0
Related