How do I insert into a table from another table by matching on values?

Question

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:

/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.

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.

Answer 1

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;

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 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.

Answer 2

whelp, messing around and I've managed to answer my own question. First, it's supposed to be an update (duh); second:

update books set author_id = (select author_id from authors where books.author = authors.author_name);