SQL Update从一个表到另一个表

I have looked up how to do this and found answers here.

我已经查找了如何做到这一点，并在这里找到答案。

I have set up a basic MS Access 2013 database to test this and have tried both solutions.

我已经设置了一个基本的MS Access 2013数据库来测试它并尝试了两种解决方案。

When I try to run the below:

当我尝试运行以下内容时：

UPDATE 
Tbl1 as A
INNER JOIN tbl2 AS B ON A.userID = B.UserID 
SET A.userID = B.[UserID];

I get a message box saying that I am about to update 0 rows. Even though there is UserID data in tbl2.

我收到一个消息框，说我要更新0行。即使在tbl2中有UserID数据。

When I try:

当我尝试：

UPDATE A 
SET A.[UserID] = B.[UserID]
FROM tbl1 A, tbl2 B WHERE A.[UserID] = B.[UserID]

I get a "Syntax error (missing operator) in query expression"

我在查询表达式中遇到“语法错误（缺少运算符）”

I did note that someone mentioned in the comments that the second solution wouldn't work in Access 2013 but like I said, the first solution isn't working either.

我注意到有人在评论中提到第二个解决方案在Access 2013中不起作用，但就像我说的那样，第一个解决方案也不起作用。

Does anyone know where I'm going wrong?

有谁知道我哪里出错了？

3 个解决方案

#1

Try

尝试

UPDATE tbl1 SET userid = (SELECT userid FROM tbl2)

UPDATE tbl1 SET userid =（SELECT userid FROM tbl2）

#2

"I get a message box saying that I am about to update 0 rows. Even though there is UserID data in tbl2."

“我收到一个消息框，说我要更新0行。即使tbl2中有UserID数据。”

Because if updating where A.userID = B.[UserID] then A.userID = B.UserID updates 0 rows.

因为如果更新A.userID = B. [UserID]，则A.userID = B.UserID更新0行。

If you were joining on something like a userName then you might be doing something.

如果您正在加入类似userName的东西，那么您可能正在做某事。

UPDATE Tbl1 as A
INNER JOIN tbl2 AS B ON A.userName = B.userName 
SET A.userID = B.[UserID];

#3

As to what i see here is you are trying to set the values of columns which you are also using in the where clause. While in the answer you quoted, the columns being set is different than those using in the where clause, hence 0 rows update message.

至于我在这里看到的是你正在尝试设置你也在where子句中使用的列的值。在您引用的答案中，设置的列与where子句中使用的列不同，因此0行更新消息。

#1

Try

尝试