如何使用一个查询更新共享一个字段的多个表中的记录？

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.

我有一个类似论坛网站的数据库。它有一个'用户'表,它与外键链接到其他表('posts','votes'等)。

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):

我正在实现一个功能,将两个用户合并到其中一个用户,这需要重新链接所有表。所以我想构造一个这样的查询(在这种情况下,user_id = 1是未触摸用户的id,user_id = 2是正在合并到第一个用户的id):

UPDATE posts, votes ... SET user_id=1 WHERE user_id=2

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

我自然会得到一个错误:“字段列表中的列'user_id'是不明确的”。

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.

编辑:实际的表数约为6,所以如果你能在答案中考虑到这一点,我将非常感激。

2 个解决方案

#1

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

尝试以[Table_Name] .Column格式设置您的值。

Instead of using user_id try using posts.user_id

而不是使用user_id尝试使用posts.user_id

#2

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.

如果需要更新多个表,请按顺序执行更新。

#1