How to update records from multiple tables sharing one field with one query?

I have a database for a forum-like website. It has a 'users' table, which is linked to other tables ('posts', 'votes', etc.) with foreign keys.

I am implementing a feature for merging two users into one of them, which requires relinking all tables. So I'd like to construct a query like this (in this case, user_id=1 is the id of the untouched user and user_id=2 is the id of the user that is being merged into first one):

UPDATE posts, votes ... S开发者_开发技巧ET user_id=1 WHERE user_id=2

I am, naturally, getting an error: "Column 'user_id' in field list is ambiguous".

My question is: how can I achieve the required result with just one query? (If possible)

EDIT: Actual number of tables is about 6, so if you could take that into account in your answer, I'd be very grateful.

Try set your value in [Table_Name].Column format.

Instead of using user_id try using posts.user_id

Something like the following should do.

    UPDATE posts SET user_id=votes.user_id
    FROM posts INNER JOIN votes ON posts.something=votes.something

Perform the updates in sequence if more than one table needs to be updated.