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