CODEWITHSUNDEEP
ms-access
Coding Duchess
Coding Duchess

Reputation: 6919

Could not delete from specified tables

I need to delete all the data from Table1 that has a corresponding ID in Table2. I used:

DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID

I ran it and got an error message "Could not delete from specified tables"

I also tried some suggestion I found here and changed the query to

DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table2.ID=Table1.ID

But got the same eerror

Upvotes: 0

Views: 3284

Answers (1)

Andre
Andre

Reputation: 27644

Apparently your JOIN has created a query that is not updateable. This is usually because one or both IDs are not primary keys or have no unique index.

More reasons can be found here or here.

If this cannot be corrected, use an IN clause instead:

DELETE Table1.*
FROM Table1 
WHERE Table1.ID IN (SELECT Table2.ID FROM Table2)

It will be slower than a INNER JOIN, but should work.

Upvotes: 4

Related Questions