Question 20946 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search