How to remove string replication with SQL?

Question

I have created a table with a author field as a string, but then I realized that there should be another table and author should be a foreign key pointing to that table.

CREATE TABLE post (
  id SERIAL INT NOT NULL PRIMARY KEY,
  author VARCHAR(80) NOT NULL
)

CREATE TABLE author (
  id SERIAL INT NOT NULL PRIMARY KEY,
  name VARCHAR(80) NOT NULL
)

Now, I am curious how to move the author data from post table to author, I have written this so far:

ALTER TABLE post ADD COLUMN author_id;
INSERT INTO author (name) SELECT author FROM post;

But how to make post.author_id to point to the correct rows in author table? I have tried this:

update post
set author_id = author.id
from post p
inner join author
on p.author = author.name;

As per this questoion, but after executing that all rows in post are now pointing to author 1! Then I thought I have to add where clause:

update post
set author_id = author.id
from post p
inner join author
on p.author = author.name
where author.name = p.author;

But again this produced the exact result from the previous SQL.

Where am I going wrong? Can you please point me to the right way.

score 1 · Accepted Answer · answered May 16 '21 at 10:51

In Postgres, you don't want an explicit join. That is, you want to mention the post table only once:

update post p
    set author_id = a.id
from author a
where p.author = a.name;

The multiple mentions to post are separate, which means the query is doing:

post cross join
post p join
author a

How to remove string replication with SQL?

1 Answers1