SQL 使用 JOIN 时如何在 MS Access 中删除？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5585732/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete in MS Access when using JOIN's?
提问by Curtis Inderwiesche
I am attempting to use the DELETEclause in MS Access and have an issue when also using the JOINclause. I have notice this can be accomplished by using the DISTINCTROWkey word.
我试图DELETE在 MS Access 中使用该子句，并且在使用该JOIN子句时遇到问题。我注意到这可以通过使用DISTINCTROW关键字来完成。
For example, the following SQL statement does not allow for deletion:
例如，下面的 SQL 语句不允许删除：
DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.Name=Table2.Name;
However, this statement does:
但是，此语句确实：
DELETE DISTINCTROW Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.Name=Table2.Name;
- Why does the
DELETEwork when using theDISTINCTROWkey word? - More specifically, what is happening in the JET engine to require this?
- 为什么
DELETE在使用DISTINCTROW关键字时起作用？ - 更具体地说，在 JET 引擎中发生了什么需要这样做？
采纳答案by Thomas
Delete Table1.*
From Table1
Where Exists( Select 1 From Table2 Where Table2.Name = Table1.Name ) = True
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.
为了扩展我的回答，官方 SQL 规范没有专门规定在操作查询中使用连接，因为它会产生不明确的结果。因此，如果您可以像我在这里那样避免在操作查询中使用联接，那就更好了（而且 Access 更快乐）。Access 需要 DISTINCTROW 的原因是两个表之间的连接很可能会创建 Table1 行的重复项（即，Table2 中有多个相关行），因此 Access 会感到困惑。我还发现，如果您尝试使用 Join 并且主键不存在，Access 会犹豫不决。一般来说，如果可以，最好避免在操作查询中加入。
回答by MarcusFey
One problem to be aware of: This does NOT work with table/query aliases!
要注意的一个问题：这不适用于表/查询别名！
DELETE a.*
from tblA as A
where exists (select 1 from tblB as B where a.id=b.id)
Deletes ALL records in tblA! I tried it using alias for tblA and tblB seperately - same result (Access 2010).
删除 tblA 中的所有记录！我分别尝试使用 tblA 和 tblB 的别名 - 结果相同（Access 2010）。
Happens with SELECT as well (which I often use before deleting)...
SELECT 也会发生（我在删除之前经常使用）...
回答by user7047561
DELETE a.*
FROM tblA AS A
WHERE EXISTS (SELECT 1 FROM tblB AS B WHERE a.id=b.id)
try this
尝试这个
DELETE tblA
FROM tblB
WHERE EXISTS (SELECT * FROM tblA AS A,tblB AS B WHERE A.id=B.id)
