CODEWITHSUNDEEP
mysqlsqlsql-insert
robtot
robtot

Reputation: 1021

Get all ID's from one table and insert values in another table based on it

I have two tables:

Table 1: Companies (id, name) Table 2: Holidays (id, companyId, name)

Currently companies table have data, but holidays doesn't. I want to iterate through all companies, get their ID's and insert two records in holidays for every company. So this would be before and after:

Before:

Companies
| id | name  |
|  0 | test1 |
|  1 | test2 |

Holidays: Empty table

After:

Companies
| id | name  |
|  0 | test1 |
|  1 | test2 |

Holidays:
| id | companyId | name     | 
|  0 |         0 | holiday1 |
|  1 |         0 | holiday2 |
|  2 |         1 | holiday1 |
|  3 |         1 | holiday2 |

Upvotes: 0

Views: 1672

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

I think you want:

insert into holidays (companyId, name)
    select c.companyId, h.name
    from companies c cross join
         (select 1 as ord, 'holiday1' as name union all
          select 2 as ord, 'holiday2'
         ) h
    order by c.companyId, h.ord;

This assumes that holidays.id is an auto-incremented column. If not, you should make it one. If not, though, you can use row_number():

insert into holidays (id, companyId, name)
    select row_number() over (order by c.companyId, h.ord),
           c.companyId, h.name
    from companies c cross join
         (select 1 as ord, 'holiday1' as name union all
          select 2 as ord, 'holiday2'
         ) h
    order by c.companyId, h.ord;

Or a parameter:

insert into holidays (id, companyId, name)
    select (@rn := @rn + 1) as id,
           c.companyId, h.name
    from companies c cross join
         (select 1 as ord, 'holiday1' as name union all
          select 2 as ord, 'holiday2'
         ) h cross join
         (select @rn := 0) params
    order by c.companyId, h.ord;

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You need a self-join for Companies table and an iteration logic to produce id column values for Holidays. So consider using :

insert into Holidays(id,company_id,name)
select @rn := @rn + 1, c1.id, concat('Holiday',(c1.id+1))
  from Companies c1
  join Companies c2
  join (select @rn := -1) as q_iter;

Demo

Upvotes: 1

phpd
phpd

Reputation: 551

Assuming Holidays.id is set to auto increment:

insert into Holidays (select id as companyId, 'holiday1' as name from Companies);
insert into Holidays (select id as companyId, 'holiday2' as name from Companies);

Upvotes: 1

Related Questions