MS Access中的SQL Update问题-操作必须使用可更新的查询 [英] SQL Update woes in MS Access - Operation must use an updateable query

问题描述

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

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:

如果我的第一个表中有"J1/2"之类的字段，它将在不同的表中查找记录的ID，并在相应的字段中包含J1和J2.

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.

一切正常.

现在，我想更新原始表，因此不再需要使用此字符串操作进行查找，但是我对更新查询的尝试以操作必须使用可更新的查询"结尾

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"

有什么想法吗?

我的SELECT语句:

My SELECT statement:

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*";

回想-正常工作，我从另一端得到了必要的t2.ID.

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

所以我想做类似的事情:

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*";

仅此操作失败.这是MS Access本身内部的问题，因此我无法想象像大多数操作必须使用可更新的查询"问题那样的实际权限问题.

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.

尝试简化不起作用的情况.

Trying to simplify the case that doesn't work.

此UPDATE查询很好:

This UPDATE query is fine:

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

这一次失败了(感谢Goedke-此示例显然失败了，因为子查询返回了1个以上的结果.我简化了尝试找到我的问题的方法)

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)

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?

此SELECT语句也可以:

This SELECT statement is fine too:

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

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

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

我现在要做的最简单的情况-使用各种SELECT语句，我现在有了一个只有两列的表-t1的主键和我想插入t1的值.

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.

我似乎还是不能写

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

其中t1的主键是f1.即使添加WHERE t1.f1 IN(从t2选择SELECT f1)也无济于事. (添加是为了消除子查询返回0个结果的可能性)

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)

推荐答案

除非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

这篇关于MS Access中的SQL Update问题-操作必须使用可更新的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！