使用 JOIN 时如何在 MS Access 中删除?

问题描述

我试图在 MS Access 中使用 DELETE 子句，但在使用 JOIN 子句时遇到问题.我注意到这可以通过使用 DISTINCTROW 关键字来完成.

I am attempting to use the DELETE clause in MS Access and have an issue when also using the JOIN clause. I have notice this can be accomplished by using the DISTINCTROW key word.

例如下面的SQL语句不允许删除:

For example, the following SQL statement does not allow for deletion:

DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.Name=Table2.Name;

但是，这个语句确实:

DELETE DISTINCTROW Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.Name=Table2.Name;

• 为什么在使用 DISTINCTROW 关键字时 DELETE 会起作用?
• 更具体地说，JET 引擎中发生了什么来要求这样做?
• Why does the DELETE work when using the DISTINCTROW key word?
• More specifically, what is happening in the JET engine to require this?

推荐答案

Delete Table1.*
From Table1
Where Exists( Select 1 From Table2 Where Table2.Name = Table1.Name ) = True

为了扩展我的回答，官方 SQL 规范没有专门规定在操作查询中使用连接，因为它会产生不明确的结果.因此，如果您可以像我在这里那样避免在操作查询中使用联接，那就更好了(而且 Access 更快乐).Access 需要 DISTINCTROW 的原因是两个表之间的 Join 很可能会创建 Table1 行的重复项(即，Table2 中有多个相关行)，因此 Access 会感到困惑.我还发现，如果您尝试使用 Join 并且主键不存在，Access 会犹豫不决.一般来说，如果可以，最好避免在操作查询中加入.

To expand on my answer, the official SQL specification does not provide for using Joins in action queries specifically because it can create ambiguous results. Thus, it is better (and Access is much happier) if you can avoid using Joins in action queries like I have here. The reason that Access wants DISTINCTROW is that it is likely that the Join between the two tables would create duplicates of Table1 rows (i.e., there are multiple related rows in Table2) and thus Access gets confused. I've also found that if you try to use a Join and a primary key does not exist Access will balk. In general, it is better to avoid a join in an action query if you can.

这篇关于使用 JOIN 时如何在 MS Access 中删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！