MySQL - Update foreign key using join?-CodePudding

I have 2 tables. In the first table i have informations about a user:

CREATE TABLE users(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32),
lastname VARCHAR(32)

);

On the second table i have informations about a post:

CREATE TABLE posts(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED,
title VARCHAR(32),

CONSTRAINT FOREIGN KEY (user_id) REFERENCES users(id)

);

What i was trying to do is update the user_id with a corresponding name from the users table.

This is something i tried to do, but i doesn't work, i am still new to databases and i am not quite sure what's going on

UPDATE posts SET user_id
FROM posts
JOIN users ON posts.user_id = users.id
WHERE users.name = 'Ricky'

CodePudding user response：

You don't need a JOIN, since the post doesn't yet have the matching user_id -- that's what you're adding.

You can use a subquery to get the ID of the named user, and use that as the value in the SET clause.

UPDATE posts
SET user_id = (SELECT id FROM users WHERE name = 'Ricky')
WHERE id = 5