使用JOIN时如何在MS Access中删除? [英] How to delete in MS Access when using JOIN's?

问题描述

我试图在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规范不提供在操作查询中使用Joins的明确规定，因为它会产生模棱两可的结果.因此，如果可以避免像我在此处那样在操作查询中使用联接，则更好(并且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中删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！