Relate two tables by one matching key in SQL?

Question

I'm pretty new to SQL and I know about FOREIGN KEY, but I can't think of a way to link two tables with each other by one key matching.

So, for example, there's a user with the "name" set as "John" and a post that has an "author" set as "John".

What query should i write to have SQL look through "users" table, find a match with post's author, and set a FOREIGN KEY to an id of that user?

Answer 1

Assuming you already have a users and a posts table, where posts have a user_id foreign key referencing users.id, you can do something like this:

update posts
join users
on posts.author = users.name
set posts.user_id = users.id;

The problem you might face is that some names might be duplicates.