MySQL Insert Into Table From Multiple Tables Using Separate SELECT Queries

Question

I have a table named people_temp_1 that contains:

Name          Age          PersonID
John          25           1
Jane          32           2
Chris         47           3

And another named people_temp_2 that contains additional information that is linked by the PersonID in table1:

ID          Profession          Location
1           Web Developer       Texas
2           Graphic Designer    North Carolina
3           Sales               California

And I want to create a new table called people that "merges" the data from both tables. How I am doing this now is:

INSERT INTO people(name, age, profession, location)
SELECT people_temp_1.name AS name,
people_temp_2.age AS age,
(SELECT people_temp_2.profession FROM people_temp_2 WHERE people_temp_2.id = people_temp_1.personId) AS profession,
(SELECT people_temp_2.location FROM people_temp_2 WHERE people_temp_2.id = people_temp_1.personId) AS location
FROM people_temp_1

As you can see I am using multiple select queries in the insert into query in order to get data that is relevant via the personId. It seems a little dirty to be doing multiple select queries when I should somehow be able to query the people_temp_2 table once and use all of its columns, but I can't figure out how to do that.

Is there a better way to structure that insert into statement?

roman · Accepted Answer · 2013-08-24 14:05:47Z

5

It's one of the basics of SQL - use join. In your case it's better to use outer join so you won't miss people from people_temp1 who don't have corresponding records in people_temp2:

insert into people(name, age, profession, location)
select
    p1.name,
    p1.age
    p2.profession,
    p2.location
from people_temp1 as p1
    left outer join people_temp2 as p2 on p2.id = p1.person_id

answered Aug 24, 2013 at 14:05

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ryandlf Over a year ago

I went with this answer because of the suggestion of outer join. Although all answer appear to be correct.

Marty McVry · Accepted Answer · 2013-08-24 14:06:48Z

0

Use a JOIN.

INSERT INTO people(name, age, profession, location)
SELECT t1.name, t1.age, t2.profession, t2.location
FROM people_temp_1 t1
INNER JOIN people_temp_2 t2
ON t1.personid = t2.id

answered Aug 24, 2013 at 14:06

Marty McVry

3,0811 gold badge22 silver badges24 bronze badges

Comments

Robert · Accepted Answer · 2013-08-24 14:11:46Z

0

Try to use join syntax as below

INSERT INTO people(name, age, profession, location)
SELECT p1.namename,
p2.age,
p2.profession,
p2.location
FROM people_temp_1 p1
JOIN people_temp_2 p2 on p2.id = p1.personId

More information about joins on mysql

edited Aug 24, 2013 at 14:11

answered Aug 24, 2013 at 14:05

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Collectives™ on Stack Overflow

MySQL Insert Into Table From Multiple Tables Using Separate SELECT Queries

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related