从Access 2007中的多个表中删除记录 [英] Delete records from multiple tables in Access 2007

问题描述

使用以下数据，我试图从两个表中删除匹配的记录.

Using the following data, I am attempting to delete matching records from both tables.

水果

ID值
1个苹果
2梨
3香蕉
4葡萄

ID Value
1 Apple
2 Pear
3 Banana
4 Grape

动物

ID值
1熊
2猴子
3苹果
4头猪

ID Value
1 Bear
2 Monkey
3 Apple
4 Pig

这两个表之间没有定义的关系.

There is no defined relationship between these two tables.

由于"Apple"出现在两个表中，所以我希望从每个表中删除该记录.

Since "Apple" appears in both tables, I would like to remove this record from each of them.

我已经尝试通过以下查询来完成此任务:

I've tried the following query to accomplish this:

DELETE DISTINCTROW Animals.*, Fruits.*
FROM Animals INNER JOIN Fruits ON Animals.Value = Fruits.Value;

但是，当我运行它时，出现以下错误:

However, when I run this, I receive the following error:

无法从指定表中删除.

Could not delete from specified tables.

我做错了什么，我该怎么解决?

What am I doing wrong, and what can I do to fix this?

推荐答案

如果没有建立关系以利用级联删除功能，您将很不走运. DELETE语句一次在一个表上工作(级联删除(如果有的话)在后台执行).您别无选择，只能设计一些东西来完成您想要的.也许，通过宏，可以做一些简单的事情，像这样:

Without establishing a relationship to take advantage of a cascading delete, you're out of luck. The DELETE statement works on one table at a time (with cascading deletes, if any, carried out behind the scenes). You have no choice but to devise something to accomplish what you want. Perhaps, via a Macro, one could do something simplistic like this:

UPDATE Animals, Fruits SET Animals.Value="DELETED", Fruits.Value="DELETED" WHERE Animals.Value=Fruits.Value
DELETE Animals WHERE Animals.Value="DELETED"
DELETE Fruits WHERE Fruits.Value="DELETED"

制作一个复杂的VBA宏(也许还有一个临时表)简直就是尽善尽美.

Short of making a sophisticated VBA macro (and perhaps a temporary table), this is about as good as it gets.

顺便说一句，我不相信即使使用SQL Server或DB2之类的功能更强大的DB也无法做到这一点.在子查询或视图上执行DELETE仍然需要DB系统将其解析为特定的表.

As an aside, I don't believe it could be done even with a more heavy duty DB such as SQL Server or DB2; a DELETE upon a subquery or view still requires that the DB system can resolve it to a particular table.

我将猜测您得到的错误是由于行锁定(由于INNER JOIN的结果).

I'm gonna guess that the error you got is a matter of locked rows (as a result of the INNER JOIN).

这篇关于从Access 2007中的多个表中删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！