如何通过匹配值从另一个表插入一个表？ [英] How do I insert into a table from another table by matching on values?

问题描述

我正在构建一个简单的图书馆应用程序。我有一张桌子叫书。

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

我没有计划与作者做任何特别的事情由于我只关心它们的名称（所以现在没有联接表，没有作者表等），但是，我发现按作者查找书籍的API端点将需要某种作者ID：

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:

/browse/author/12345

而不是

/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

，并且需要通过id列将每一行引用给其作者。我知道我将需要一个外键，但是由于books表中没有数据，因此我不能简单地一拍（所有null值，等等），无论如何我仍然需要获取所有作者ID并将其插入到正确的行。

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.

如何根据现有列中的值匹配将正确的author_ids插入books表中？我试过了：

How can I insert the correct author_ids into the books table based on matching the value in the existing columns? I tried:

insert into books (author_id) select author_id from authors where (books.author == authors.author_name);

但是可以预见的是，这太天真了。

But predictably that's too naive.

推荐答案

您可以在 UPDATE 语句，允许使用以下首选形式：

You can join additional tables in an UPDATE statement, allowing for this preferable form:

UPDATE books b
SET    author_id = a.author_id
FROM   authors a
WHERE  b.author = a.author_name;

三个原因：

• 更安全。您的查询将在找不到匹配作者的每一行中写入一个NULL值。在您看来，这无关紧要，但是有可能导致类似查询中的数据丢失，因为您已经在要更新的列中有数据。
  如果找不到匹配的作者，我的选择不做任何事情。

• 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 subqueries like 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.

这篇关于如何通过匹配值从另一个表插入一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！