Delete record if a matching record is present in another table

[Kiwi_731]

Dear Experts

I have a query which is meant to delete any records in tableA which have a
matching field in tableB, thus:

DELETE TableA.*, TableB.ID_NO
FROM TableA INNER JOIN TableB ON TableA.ID_NO=TableB.ID__NO;

(The above was the SQL code generated by creating the delete query in
Design view).

As it happens, in this case there was only one such record out of a total of
about 1200 records. The record came up when I viewed the query before running
it.

On running the query however, I got the message: “Could not delete from
specified tablesâ€. *However*, when I added the keyword DISTINCTROW after the
DELETE, everything worked as expected. Thanks to my colleague for the
inspiration, but I still feel none the wiser.

Can someone explain what is going on here?

TIA

Hopeful Kiwi

 

[John Spencer]

DELETE TableA.*, TableB.ID_NO
FROM TableA INNER JOIN TableB ON TableA.ID_NO=TableB.ID_NO;

The above statement has a problem because the Access does not know whether
to delete from TableA or TableB since fields from both are in the DELETE
clause

It might work with
DELETE TableA.*
FROM TableA INNER JOIN TableB ON TableA.ID_NO=TableB.ID_NO;

As you found out DistinctRow can make your original query work, but not in
all cases.

Another alternative would be
DELETE FROM TableA
WHERE TableA.ID_NO in
(SELECT ID_No FROM TableB)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Kiwi_731]

I have tried both your suggestions. The first one produced the same error;
however, the second one worked OK, so thanks for that.

Why does DISTINCTROW make a difference?

Hopeful Kiwi

 

[Kiwi_731]

Follow up - there is MSKB article about this:
http://support.microsoft.com/kb/240098

If I understand it correctly, it basically says that a query can't delete a
record if it it retrieved more than once by the query, or could be retrieved
more than once. (As I say in this case, only once occurrence was retrieved,
even without using DISTINCTROW).

I am still not quite happy that I understand the explanation, but then again
I am a newcomer to all this. I will let the unconscious digest it - it may
well become blindingly obvious...

Hopeful Kiwi