从另一个表获取ID后插入表 [英] Insert into a table after getting an ID from another table

问题描述

给出以下两个示例表：

authors(
  id SERIAL PRIMARY KEY,
  name VARCHAR(60) UNIQUE,
  profession VARCHAR(30)
)

posts(
  id SERIAL PRIMARY KEY,
  text TEXT,
  author_id INT REFERENCES authors(id)
)

authors表包含所有作者，但是现在，我正在尝试填充posts表，我有一组数据，这些数据基本上都有一个具有作者名的帖子，但是我想查询author_id，以便可以按如下所示插入Quotes表中： / p>

The authors table has all of the authors, but now that I'm trying to populate the posts table, I have a set of data that basically has a post with an author name, but I'd like to query the author_id so that I can insert into the quotes table as follows:

INSERT INTO posts(text, author_id) VALUES(
  /* 
     pseudo broken-code
     SELECT post, author FROM posts_temp (where all of the data temp resides),
     SELECT id FROM authors WHERE authors.name = author.name (obviously this is wrong, but the idea is getting the id from the authors table that matches the author name from the selection above)
  */

)

推荐答案

INSERT 语句可以使用 SELECT 语句返回的行作为插入数据的源。因此，从 posts_temp 和 authors 构造适当的 SELECT 语句，然后然后您完成了：

An INSERT statement can use the rows returned by a SELECT statement as source for inserting data. So construct the appropriate SELECT statement from posts_temp and authors and then you are done:

INSERT INTO posts(text, author_id)
  SELECT pt.post, a.id
  FROM posts_temp pt
  JOIN authors a ON a.name = pt.author;

这篇关于从另一个表获取ID后插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！