MS Access 中的 SQL 更新问题 - 操作必须使用可更新查询 [英] 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 语句，我现在有一个只有 2 列的表 - 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 (SELECT f1 FROM t2) 也无济于事.(添加以消除子查询返回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)

推荐答案

(SELECT ID FROM t2)的子查询不能工作，除非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);

失败了，保罗收到了同样的信息.

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 MVPs 建议用 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 或更高版本的后端时可以避免该错误.(访问更新子查询"的谷歌结果支持这个理论).

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 更新问题 - 操作必须使用可更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！