postgresql 如何通过匹配值从另一个表插入到一个表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17118658/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I insert into a table from another table by matching on values?
提问by whiterook6
I'm building a simple library application. I had a table called books; among its columns are:
我正在构建一个简单的库应用程序。我有一张桌子，叫做书；其中的栏目有：
books:
book_id | integer | not null default nextval('books_book_id_seq'::regclass)
title | text | not null
author | text | not null default 'unknown'::text
I hadn't planned on doing anything special with the authors since all I care about them are their names (so no join table, no authors table, etc.) NOW, however, I discover that the API endpoint for finding books by author will need some sort of author id:
我没有计划对作者做任何特别的事情，因为我只关心他们的名字（所以没有连接表，没有作者表等）现在，但是，我发现用于按作者查找书籍的 API 端点将需要某种作者 ID：
/browse/author/12345
instead of
代替
/browse/author/Arthur%20C%20Clarke (or whatever)
I created a separate table for authors:
我为作者创建了一个单独的表：
authors:
author_id | integer | not null default nextval('authors_author_id_seq'::regclass)
author_name | text | not null
and need to refer each book row to its author via the id column. I know I'll need a foreign key, but since there's no data in the books table I cannot simply slap one in (all null values, etc) and in any case I still need to get all the author ids and insert them into the correct rows.
并且需要通过 id 列将每一本书行引用给它的作者。我知道我需要一个外键，但是由于书籍表中没有数据，我不能简单地插入一个（所有空值等），无论如何我仍然需要获取所有作者 ID 并将它们插入正确的行。
How can I insert the correct author_ids into the books table based on matching the value in the existing columns? I tried:
如何根据匹配现有列中的值将正确的 author_ids 插入到书籍表中？我试过：
insert into books (author_id) select author_id from authors where (books.author == authors.author_name);
But predictably that's too naive.
但可以预见的是，这太天真了。
回答by Erwin Brandstetter
You can join additional tables in an UPDATEstatement, allowing for this preferable form:
您可以在一个UPDATE语句中加入额外的表，允许使用这种更可取的形式：
UPDATE books b
SET author_id = a.author_id
FROM authors a
WHERE b.author = a.author_name;
Three reasons:
三个原因：
It's safer. Your query will write a NULL value in every row where no matching author is found. That doesn't seen to matter in your case, but can potentially lead to data loss in similar queries where you already have data in the column to be updated. My alternative does nothing if no matching author is found.
It's faster. The above for one. But also because correlated subquerieslike you have scale terribly. Joining in a table is generally faster, especially with more than a few rows.
It's cleaner and more easily adapted to additional columns.
这样更安全。您的查询将在找不到匹配作者的每一行中写入一个 NULL 值。这在您的情况下并不重要，但可能会导致类似查询中的数据丢失，其中您已经在要更新的列中有数据。如果找不到匹配的作者，我的替代方案将不起作用。
它更快。以上为一。但也因为像您这样的相关子查询具有非常大的规模。加入一个表通常会更快，尤其是多行。
它更干净，更容易适应额外的列。
回答by whiterook6
whelp, messing around and I've managed to answer my own question. First, it's supposed to be an update (duh); second:
whelp，乱搞，我设法回答了我自己的问题。首先，它应该是一个更新（废话）；第二：
update books set author_id = (select author_id from authors where books.author = authors.author_name);
