MS Access 中的 SQL 更新问题 - 操作必须使用可更新的查询

SQL Update woes in MS Access - Operation must use an updateable query

提问by Paul Smith

I have a select query which does some text manipulation to essentially reformat a field so that I can look it up in another table:

我有一个选择查询，它执行一些文本操作来基本上重新格式化一个字段，以便我可以在另一个表中查找它：

If my first table if I have a field like "J1/2" it looks up the ID of a record in a different table with J1 and J2 in the appropriate fields.

如果我的第一个表有一个像“J1/2”这样的字段，它会在不同的表中查找记录的 ID，其中 J1 和 J2 在适当的字段中。

This all works well.

这一切都很好。

Now I want to update the original table so I don't have to do lookups using this string manipulation anymore, but my attempts at update queries end with "Operation must use an updateable query"

现在我想更新原始表，这样我就不必再使用此字符串操作进行查找了，但是我尝试更新查询以“操作必须使用可更新查询”结束

Any ideas?

有任何想法吗？

My SELECT statement:

我的 SELECT 语句：

SELECT DISTINCT
t1.DD,
t1.TN,
t1.DD & " J" & MID(t1.TN,2,1) AS CalculatedStart,
t1.DD & " J" & MID(t1.TN,4,1) AS CalculatedEnd,
t2.ID
FROM t1 INNER JOIN t2
ON (t1.DD & " J" & MID(t1.TN,2,1)=t2.StartLink)
AND (t1.DD & " J" & MID(t1.TN,4,1)=t2.EndLink)
WHERE t1.TN Like "J?/?"
AND t1.DD Like "M*";

Recall - this works fine and I get the necessary t2.ID out the other end.

回想一下 - 这很好用，我在另一端得到了必要的 t2.ID。

So I want to do something like:

所以我想做一些类似的事情：

UPDATE t1 SET t2ID = (
    SELECT Query1.ID
    FROM Query1
    WHERE t1.DD=Query1.DD
    AND t1.TN=Query1.TN
    )
WHERE t1.TN Like "J?/?"
AND t1.DD Like "M*";

Only this fails. This is within MS Access itself so I can't imagine an actual permissions problem like most of the "Operation must use an updateable query" problems seem to be.

只有这个失败了。这是在 MS Access 内部，所以我无法想象像大多数“操作必须使用可更新查询”问题那样的实际权限问题。

EDIT: Trying to simplify the case that doesn't work.

编辑：试图简化不起作用的情况。

This UPDATE query is fine:

这个 UPDATE 查询很好：

UPDATE t1
SET t2ID="Unknown"
WHERE TN LIKE "J?/?"
AND DD LIKE "M*";

This one fails (Thanks Goedke - this example obviously fails because the subquery returns more than 1 result. I had oversimplified to try to find my problem)

这个失败了（感谢 Goedke - 这个例子显然失败了，因为子查询返回了 1 个以上的结果。我已经过分简化以试图找到我的问题）

UPDATE t1
SET t2ID=(SELECT ID FROM t2)
WHERE TN LIKE "J?/?"
AND DD LIKE "M*";

So do I just have my subquery syntax wrong in some way?

那么我的子查询语法是否以某种方式错误？

EDIT: This SELECT statement is fine too:

编辑：这个 SELECT 语句也很好：

SELECT t1.OA, t1.DD, t1.TN, t1.HATRIS,
    query1.DD, query1.TN, query1.ID
FROM t1 INNER JOIN query1
ON t1.DD=query1.DD
AND t1.TN=query1.TN

Furthermore, using count on the select statement above shows that there is exactly 1 ID being returned per (DD,TN) combination

此外，在上面的 select 语句上使用 count 表明每个 (DD,TN) 组合正好返回 1 个 ID

EDIT:

编辑：

The simplest case I've now got to - using various SELECT statements I now have a table with just 2 columns - the primary key of t1 and the value I want to insert into t1.

我现在遇到的最简单的情况 - 使用各种 SELECT 语句，我现在有一个只有 2 列的表 - t1 的主键和我想插入到 t1 中的值。

I still can't seem to write

我似乎还是不会写

UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)

where t1's primary key is f1. Even adding WHERE t1.f1 IN (SELECT f1 FROM t2) doesn't help. (Added to eliminate the possibility that the subquery returns 0 results)

其中 t1 的主键是 f1。即使添加 WHERE t1.f1 IN (SELECT f1 FROM t2) 也无济于事。（添加来消除子查询返回0结果的可能性）

采纳答案by Godeke

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?

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

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 破坏了有关键的信息，因此如果它被绑定到一个表单，该表单将无法保存回来）。

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.

你在那里使用 DISTINCT 的事实会让我怀疑子查询在你更复杂的例子中返回了不止一行。这可能是从子查询结果中分配最常见的问题：对 where 子句的约束不足。

Another problem I have seen with assigning out of a subquery is if the syntaxof the inner query is incorrect. At least with SQL 2000 and 2005 back ends, the query processor will silentlyfail 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.)

我在分配子查询时遇到的另一个问题是内部查询的语法是否不正确。至少对于 SQL 2000 和 2005 后端，在这种情况下，查询处理器将默默地失败并返回 NULL。（据我所知，这是一个错误：我认为没有理由在子查询中静默允许在顶级返回错误的内容......但它确实存在。）

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

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

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

I did notput 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.

我并没有把任何约束除了在ID和ID2一个主键。所有字段都是文本，这与我通常用于 ID 的不同，但应该无关紧要。

t1:

t1：

ID  FK  Data
Key1        Data1
Key2        Data2
Key3        Data3

t2:

t2：

ID2 Data2
Key1    DataA
Key2    DataB
Key3    DataC

A query of the form:

表单查询：

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

Failed with the same message Paul got.

失败并显示 Paul 收到的相同消息。

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.

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

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:

注意：如果我将数据库后端从本机更改为 SQL 2005，则更新有效！稍微搜索了一下，我发现 Access MVP 建议使用 DLOOKUP 来替换子查询：

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

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

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

显然，这是 Access SQL 中的一个错误，在使用 SQL Express 2000 或更高版本的后端时可以避免。（“访问更新子查询”的谷歌结果支持这一理论）。

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

请参阅此处了解如何使用此解决方法：http: //www.techonthenet.com/access/functions/domain/dlookup.php

回答by Ben McIntyre

I have to weigh in with David W. Fenton's comment on the OP.

我必须权衡 David W. Fenton 对 OP 的评论。

This is highly annoying problem with Jet/ACE. But try either:

这是 Jet/ACE 非常烦人的问题。但请尝试：

1. go to the query properties (click the background of the pane where the tables are displayed) and set 'Unique Records' to 'Yes'
2. Option 1 is the equivalent of adding the somewhat strange looking DISTINCTROWkeyword to the SELECTclause, eg

1. 转到查询属性（单击显示表的窗格的背景）并将“唯一记录”设置为“是”
2. 选项 1 相当于DISTINCTROW在SELECT子句中添加看起来有些奇怪的 关键字 ，例如

:

：

UPDATE DISTINCTROW tblClient 
       INNER JOIN qryICMSClientCMFinite 
          ON tblClient.ClientID = qryICMSClientCMFinite.ClientID
   SET tblClient.ClientCMType = "F";

This solves so many problems involving this error message that it is almost ridiculous.

这解决了许多涉及此错误消息的问题，这几乎是荒谬的。

That's MS Access in a nutshell - if you don't know the trade-secret workaround for problem x, you can take days trying to find the answer. To know the 10,000 workarounds IS to program Access. Is that enough of a warning for the uninitiated ?

简而言之，这就是 MS Access - 如果您不知道问题 x 的商业秘密解决方法，您可能需要数天时间才能找到答案。了解 10,000 种解决方法就是对 Access 进行编程。对不熟悉的人来说，这足以警告吗？

Ben

本

回答by David Leigh

This worked for me (Access 2000)

这对我有用（Access 2000）

UPDATE DISTINCTROW T1 inner join T2 on T2.f1 = T1.f1  SET f2 = f2;

回答by Sandip

I havent't read the whole thread, but this is the solution that I am using:

我还没有阅读整个线程，但这是我正在使用的解决方案：

update (select * from t1 inner join t2 on t1.key = t2.key) set t1.field1 = t2.field2

and that works fine in MS Access for me.

这对我来说在 MS Access 中工作正常。

回答by Plamen

My solution was to change my sql on that way.

我的解决方案是以这种方式更改我的sql。

  update (select o.pricein, g.pricein from operations o left join goods g on g.id = o.goodid where o.opertype = 4 and o.acct = 1) 
  set o.pricein = g.pricein

回答by user3775507

I had the same error ("Operation must use an updateable query") using Access 2010 and I was performing a simple update query with an inner join. All I did was add a primary key to the table I was joining on (already had one, of course, on the table I was updating) and everything worked.

我在使用 Access 2010 时遇到了同样的错误（“操作必须使用可更新的查询”），并且我正在使用内部联接执行简单的更新查询。我所做的只是在我加入的表中添加一个主键（当然，我正在更新的表上已经有了一个主键）并且一切正常。

回答by JeffO

For this one: UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)

对于这个：UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)

UPDATE t1 INNER JOIN t2 ON t1.f1 = t2.f1 SET t1.f2 = [t2].[f2];