CODEWITHSUNDEEP
sqlms-accessvba
Demenyi Norbert
Demenyi Norbert

Reputation: 41

How to delete matching rows in two tables in ms access?

Here is my sql code in ms access:

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

I want to notice here that Table1 has many columns but Table2 just one column. I want to delete all rows in Table1 where Table1.Field1 = Table2.Field1. But when I execute the code I get this error: Could not delete from specified tables.

However if I use SELECT instead of DELETE it works fine for displaying rows:

SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table1.Field1  = Table2.Field1

But I would like to DELETE these rows.

Upvotes: 2

Views: 7629

Answers (3)

Parfait
Parfait

Reputation: 107767

Consider a subquery. DELETE queries in MS Access should have the delete set of rows as the full, standalone result set. In your attempt the result set is two tables even though you only view the first with SELECT clause:

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

And aligning to ANSI, the asterisk is not needed:

DELETE FROM Table1
WHERE Field1 IN (SELECT Field1 FROM Table2)

Upvotes: 4

Kay Tila
Kay Tila

Reputation: 31

I would select select the rows ids of the rows into a helper table. Then

delete from Table1 where table1.rowid = helpertable.rowid;

then

delete from Table2 where table2.rowid = helpertable.rowid;

Then 

drop helpertable;

Upvotes: 1

Erik A
Erik A

Reputation: 32682

Access has weird issues when joining fields in action queries.

Try the following to avoid the join:

DELETE *
FROM Table1
WHERE DCount('Field1', 'Table2', 'Field1 = "' & Field1 & '"') <> 0

Upvotes: 1

Related Questions