How to insert into a table from another table by matching values?

Question

I am creating a simple library application. I had a table called books; among its columns:

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 did not plan to do anything special with the authors, since all I care about them is their names (therefore there is no connection table, author table, etc.) NOW, however, I found that the API endpoint to search for books, the author will need some kind of author identifier:

 /browse/author/12345 

instead

 /browse/author/Arthur%20C%20Clarke (or whatever) 

I created a separate table for the authors:

 authors: author_id | integer | not null default nextval('authors_author_id_seq'::regclass) author_name | text | not null 

and you must pass each line of the book to your author through the id column. I know that I need a foreign key, but since there is no data in the book table, I can’t just tickle it (all null values, etc.), And in any case, I still need to get all the author IDs and paste them into correct lines.

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

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

But predictably this is too naive.

Answer 1

You can join additional tables in UPDATE given this preferred form:

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

Three reasons:

• It is safer. Your request will write a NULL value on each line where no matching author is found. This does not matter in your case, but can potentially lead to data loss in similar queries where you already have data in the column that needs to be updated. My alternative does nothing if no matching author is found.

• It's faster. Above for one. But also because the subqueries are correlated , as if you have a horrible scale. A join in a table is usually faster, especially with multiple rows.

• It is cleaner and easier to adapt to additional columns.

Answer 2

whelp, messing around, and I was able to answer my own question. First, it must be an update (duh); second:

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