MySQL - Update foreign key using join?

Question

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'

What do you want to set `user_id` to? It already has a value, since you're using that to join with the `users` table. — Barmar, Nov 23 '22 at 22:06
What do you mean by "update the user_id with a corresponding name"? `name` is a string, `user_id` is an integer, it doesn't make sense to update the integer with a string. — Barmar, Nov 23 '22 at 22:08
See https://stackoverflow.com/questions/12394506/mysql-update-table-based-on-another-tables-value for the correct syntax of `UPDATE+JOIN`. But it's not clear what you actually want to do with this. — Barmar, Nov 23 '22 at 22:10
Are you trying to change ALL the `posts.user_id` to the `id` of `Ricky`? Or maybe just the ones that are currently `NULL`. — Barmar, Nov 23 '22 at 22:36
Update one user_id where user.name Ricky exists in users table — Raitiko, Nov 23 '22 at 22:38

score 1 · Accepted Answer · answered Nov 23 '22 at 22:42

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

MySQL - Update foreign key using join?

1 Answers1