Resolved: Insert two Records in new mysql table for each record in another table

In this post, we will see how to resolve Insert two Records in new mysql table for each record in another table

Question:

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

Best Answer:

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)

If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com

Question:

Best Answer:

Related