2

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

Fruits

ID Value
1 Apple
2 Pear
3 Banana
4 Grape

Animals

ID Value
1 Bear
2 Monkey
3 Apple
4 Pig

There is no defined relationship between these two tables.

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Sesame
  • 3,370
  • 18
  • 50
  • 75

1 Answers1

4

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"

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

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.

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

rskar
  • 4,607
  • 25
  • 21
  • I don't know of any DBMS that can do this in a single statement (barring cascading deletes or triggers, which are different forms of follow-on effects, not properly part of the statement). – RolandTumble Jan 26 '11 at 18:29