I need to Insert two Records in a new mysql table for each record in another table example:
table1
id, name
1, Patrick
2, John
I want to insert favorite site for each records in the second table and each record should have facebook and google as default
the second table should looks like: table2
table1_id, site
1, facebook
1, google
2, facebook
2, google
CodePudding user response：
We can multiply the original table with a fixed list of rows with a cross join:
insert into table2 (table1_id, site)
select t1.id, s.site
from table1 t1
cross join (select 'google' site union all select 'facebook') s
In recent MySQL versions (>= 8.0.19), the VALUES statement makes the syntax neater:
insert into table2 (table1_id, site)
select t1.id, s.site
from table1 t1
cross join ( values row('google'), row('facebook') ) s(site)
CodePudding user response：
This is an other way to do it using inner join
insert into table2
select t1.id, s.site
from table1 t1
inner join (
select 'facebook' site union select 'google' site
) as s on s.site <> ''
order by t1.id;