如何通过匹配值从另一个表中插入一个表? [英] 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 端点by author 需要某种作者 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 表中没有数据，我不能简单地输入一个(所有空值等)，无论如何我仍然需要获取所有作者 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屋！