Delete unmatched query

  • Thread starter Thread starter Roy
  • Start date Start date
R

Roy

Using Access 97
I need to delete the records in TableA that don't have
corresponding records in TableB. Here's the SQL from the
query grid:

DELETE TableA.*, TableB.SKU
FROM TableA LEFT JOIN TableB ON TableA.SKU = TableB.SKU
WHERE (((TableB.SKU) Is Null));

When I preview the results, the query looks like it works.
When I run it, I get an error: Couldn't delete from
specified tables. The normal reason for the error is
permissions or the db is read-only for some reason. I
can't find where that's an issue in this case--I recreated
both tables from scratch to be sure. What am I missing?
 
DELETE TableA.*, TableB.SKU
FROM TableA LEFT JOIN TableB ON TableA.SKU = TableB.SKU
WHERE (((TableB.SKU) Is Null));
Click to expand...

Remove the TableB.SKU reference in the DELETE clause (or,
equivalently, uncheck the Show box in the query grid). As stated the
SQL will try to delete from both tables, and there's nothing to delete
from TableB; you just want to DELETE TableA.*.
 
Modified the SQL (there's no Show box on a Delete query)
and it still didn't work. I'm open to other ideas.
 
Modified the SQL (there's no Show box on a Delete query)
and it still didn't work. I'm open to other ideas.
Click to expand...

Is SKU a Primary Key in the table from which you wish to delete? It
will need to be for updatability.
 
DELETE *
FROM TableA
WHERE SKU NOT IN (SELECT SKU FROM TableB)

--
HTH

Dale Fye


Using Access 97
I need to delete the records in TableA that don't have
corresponding records in TableB. Here's the SQL from the
query grid:

DELETE TableA.*, TableB.SKU
FROM TableA LEFT JOIN TableB ON TableA.SKU = TableB.SKU
WHERE (((TableB.SKU) Is Null));

When I preview the results, the query looks like it works.
When I run it, I get an error: Couldn't delete from
specified tables. The normal reason for the error is
permissions or the db is read-only for some reason. I
can't find where that's an issue in this case--I recreated
both tables from scratch to be sure. What am I missing?
 
Thanks John and Dale for your help. Your suggestions and a
little detective work led me to the solution. TableB was a
temporary table that didn't have a primary key. Turns out
Access needs for both tables in this type of delete query
to have primary keys. The original query works fine with
that modification.
 
Back
Top