除非t2中只有一条记录，否则(SELECT ID FROM t2)的子查询将无法工作.您希望使用哪个ID?

A subquery of (SELECT ID FROM t2) can't work unless there is only one record in t2. Which ID are you expecting to be used?

当您具有联接并且不包括以数据绑定形式更新回表所必需的所有主键时，通常会报告该错误消息(例如，原始DISTINCT会破坏有关键的信息，因此如果它已绑定到表格，该表格将无法保存).

The error message that is being reported normally occurs when you have joins and are not including all of the primary keys necessary to update back to tables in a data bound form (for example, your original DISTINCT destroys information about keys, so if it was bound to a form, the form would not be able to save back).

您正在使用DISTINCT的事实使我怀疑在更复杂的示例中子查询返回的行多.这可能是从子查询结果中分配出最常见的问题:约束where子句.

The fact you are using DISTINCT there would make me suspicious that the sub query is returning more than one row in your more complex example. This is probably the most common problem with assigning out of a sub query result: under-constraining the where clause.

从子查询中分配出去的另一个问题是内部查询的语法是否不正确.至少在SQL 2000和2005后端，查询处理器将静默失败，并在这种情况下返回NULL. (据我所知，这是一个错误:我看不出有什么理由可以在子查询中默默地允许某些将在***返回错误的东西……但是确实如此.)

Another problem I have seen with assigning out of a subquery is if the syntax of the inner query is incorrect. At least with SQL 2000 and 2005 back ends, the query processor will silently fail and return NULL in such cases. (This is, as far as I can tell, a bug: I see no reason why something that will return an error at the top level would be silently permitted in a subquery... but there it is.)

为了确保Paul和我都不会发疯，我创建了以下表格:

Just to ensure that neither Paul or I wasn't going crazy, I created the following tables:

t1 | ID, FK, Data
t2 | ID2, Data2

除了ID和ID2上的主键之外，我没有没有施加任何约束.所有字段均为文本，与我通常用于ID的文本不同，但应该无关紧要.

I did not put any constraints except a primary key on ID and ID2. All fields were text, which is different from what I normally use for IDs, but should be irrelevant.

t1:

ID  FK  Data
Key1        Data1
Key2        Data2
Key3        Data3

t2:

ID2 Data2
Key1    DataA
Key2    DataB
Key3    DataC

以下形式的查询:

UPDATE t1 SET t1.FK = (select ID2 from t2 where t2.ID2 = t1.ID);

未能与Paul得到相同的消息.

Failed with the same message Paul got.

select *, (select ID2 from t2 where t2.ID2 = t1.ID) as foreign from t1, 

可以按预期工作，因此我们知道不应该责怪子查询语法.

works as expected, so we know the subquery syntax is not to blame.

UPDATE t1 SET t1.FK = 'Key1'

也可以按预期工作，因此我们没有损坏的或不可更新的目的地.

also works as expected, so we don't have a corrupt or non updateable destination.

注意:如果我将数据库后端从本机更改为SQL 2005，则此更新有效！进行了一些谷歌搜索，我发现Access MVP建议DLOOKUP替换子查询:

Note: if I change the database backend from native to SQL 2005, the update works! A bit of googling around, and I find Access MVPs suggesting DLOOKUP to replace a subquery:

http://www.eggheadcafe.com/software/aspnet/31849054/update-with-subquerycomp.aspx

显然，这是Access SQL中的错误，在使用SQL Express 2000或更高版本的后端时，可以避免该错误. (访问更新子查询"的google结果支持该理论.)

Apparently this is a bug in Access SQL, one that is avoided when using a SQL Express 2000 or higher back end. (The google results for "access update subquery" support this theory).

有关如何使用此替代方法的信息，请参见此处: http://www.techonthenet .com/access/functions/domain/dlookup.php

See here for how to use this workaround: http://www.techonthenet.com/access/functions/domain/dlookup.php