Re: Specify the table containing the records you want to delete

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I've been told - never tested this - that using an exists clause is faster. That query would look like the following. It may or may not be faster, but it is something you could test.

It might be faster if there were only a few records in Table1 and a lot of records in table2. And if the reverse were true it might be considerably slower.

DELETE Table1.*
FROM Table1
WHERE Exists
(SELECT *
FROM Table2
WHERE Table2.PrimaryKeyField = Table1.PrimaryKeyField)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Stewart Berman wrote:
I missed the absence of Table2 in the FROM clause. I tried it the way you specified it and it
worked.

I still need to get a fix for the join not working as the work around is significantly slower than a
join on two primary keys but at least I have something to use for now.

Thanks.

John Spencer <spencer@xxxxxxxxx> wrote:

You tried
DELETE Table1.*
FROM Table1, Table2
WHERE Table1.PrimaryKeyField IN (SELECT PrimaryKeyField FROM Table2);

You were told to try
DELETE Table1.*
FROM Table1
WHERE Table1.PrimaryKeyField IN (SELECT PrimaryKeyField FROM Table2);

NOTE there is only ONE table in the FROM clause of the Delete query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Stewart Berman wrote:
I already tried that -- same error.

DELETE Table1.*
FROM Table1, Table2
WHERE Table1.PrimaryKeyField IN (SELECT PrimaryKeyField FROM Table2);

Results: Could not delete from the specified tables

Dale Fye <dale.fye@xxxxxxxxxx> wrote:

Let me get this right. You want to delete all of the records from Table1 where there is a match between the primary key fields in table 1 and table2.

My recommendation would be:

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

----
HTH
Dale



"Stewart Berman" wrote:

Access 2007

I am getting a "Specify the table containing the records you want to delete" error when trying to
delete rows in a linked table containing an attachment field using an inner join.

I have reproduced the problem using a table structure with two fields:
PrimaryKeyField Autonumber
Picture Attachment

The query is:
DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.PrimaryKeyField = Table2.PrimaryKeyField;

With the attachment field in either table the query fails. If I remove the Attachment field from
both tables the query works.

Is this a known problem and if so how do I get around it?

.

Relevant Pages