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):

expected example output in phonenrs table:

CodePudding user response：

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

CodePudding user response：

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.