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

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

如果我有第一个表,如果我有一个像"J1/2"这样的字段,它会在相应字段中查找J1和J2的不同表中记录的ID.

这一切都运作良好.

现在我想更新原始表,所以我不必再使用这个字符串操作进行查找,但是我对更新查询的尝试以"操作必须使用可更新的查询"结束

有任何想法吗？

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

回想一下 - 这很好,我从另一端得到了必要的t2.ID.

所以我想做一些事情:

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本身,所以我无法想象像大多数"操作必须使用可更新的查询"问题似乎是一个实际的权限问题.

编辑:试图简化不起作用的情况.

这个UPDATE查询很好:

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

这个失败了(感谢Goedke - 这个例子显然失败了,因为子查询返回的结果超过了1个.我过于简单了,试图找到我的问题)

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

那么我的子查询语法在某种程度上是错误的吗？

编辑:这个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

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

编辑:

我现在最简单的情况 - 使用各种SELECT语句我现在有一个只有2列的表 - t1的主键和我要插入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结果的可能性)

1> Ben McIntyre..：

---

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

这是Jet/ACE非常烦人的问题.但尝试:

转到查询属性(单击显示表格的窗格的背景)并将"唯一记录"设置为"是"

选项1相当于DISTINCTROW在SELECT子句中添加有些奇怪的 关键字 ,例如

:

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

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

简而言之,这就是MS Access - 如果您不知道问题x的商业秘密解决方法,您可能需要数天时间才能找到答案.要知道10,000个解决方法是对Access进行编程.这对于没有经验的人来说是否足够警告？

本

2> 小智..：

---

这对我有用(Access 2000)

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

3> Godeke..：

---

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

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

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

我在分配子查询时看到的另一个问题是内部查询的语法是否不正确.至少在SQL 2000和2005后端,查询处理器将静默失败并在这种情况下返回NULL.(据我所知,这是一个错误:我认为没有理由为什么会在子查询中默默地允许在顶层返回错误的东西......但它确实存在.)

编辑:为了确保Paul或我都没有发疯,我创建了以下表格:

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

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

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得到了同样的信息失败了.

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

按预期工作,所以我们知道子查询语法不应该受到责备.

UPDATE t1 SET t1.FK = 'Key1'

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

注意:如果我将数据库后端从本机更改为SQL 2005,则更新有效!有点谷歌搜索,我发现Access MVP建议DLOOKUP替换子查询:

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

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

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

4> 小智..：

---

我没有读完整个帖子,但这是我正在使用的解决方案:

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

这对我来说在MS Access中运行良好.