Update and join query

Question

I had a table named stores whose column named id which had some values are to be updated by user_id values from user table.

update stores s
join users u on u.user_id = s.id
set s.id = u.user_id
where s.id = 100;

but the above query does no change to the column values of s.id.

Answer 1

You can avoid ON condition since you are limiting the result to 1 id, forthermore if you tell the database to look for id same as id in the other table you couldn't expect it to change. Also you are selecting id from the same table where you want to perform the update so I think you meant to select from table users

update stores s
join users u 
set s.id = u.user_id
where u.user_id = 100;