MySQL使用单独的SELECT查询从多个表插入表中

I have a table named people_temp_1 that contains:

我有一个名为people_temp_1的表,其中包含:

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:

另一个名为people_temp_2,其中包含由table1中的PersonID链接的其他信息:

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:

我想创建一个名为people的新表,它“合并”来自两个表的数据。我现在如何做到这一点:

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.

正如您所看到的,我在插入查询中使用多个选择查询,以便通过personId获取相关的数据。当我应该能够以某种方式查询people_temp_2表并使用其所有列时,做多个选择查询似乎有点脏,但我无法弄清楚如何做到这一点。

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

是否有更好的方法来构造插入语句?

3 个解决方案

#1

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:

这是SQL的基础之一 - 使用join。在你的情况下,最好使用外连接,这样你就不会错过people_temp2中没有相应记录的people_temp1中的人:

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

#2

Use a JOIN.

使用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

#3

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

有关mysql连接的更多信息

#1