Need help constructing delete subquery

[Guest]

I'm trying to delete all records in a table called "Temp_Person" from a
larger table called "Entity_Contact_Join."

Both tables contain two primary key fields "Entity ID" and "Contact ID," so
basically what I'm trying to say is delete every record from
"Entity_Contact_Join" that matches both Entity ID and Contact ID from
Temp_Person.

I am able to construct a Select Query with these two fields as joins, no
problem. When I try to do a delete query, I get message "Could not delete
from specified tables."

I've tried looking up knowledge base and building subqueries based on
previous posts, but all I'm doing is getting more confused and frustrated.

Can someone please translate this SQL code into what I should use instead?

DELETE Entity_Contact_Join.*
FROM Entity_Contact_Join INNER JOIN Temp_Person ON
(Entity_Contact_Join.[Contact ID] = Temp_Person.[Contact ID]) AND
(Entity_Contact_Join.[Entity ID] = Temp_Person.[Entity ID]);

 

[Van T. Dinh]

It may not be the most efficient SQL but this should work:

========
DELETE *
FROM Entity_Contact_Join AS T1
WHERE EXISTS
(
SELECT *
FROM Temp_Person AS T2
WHERE (T2.[Entity ID] = T1.[Entity ID])
AND (T2.[Contact ID] = T1.[Contact ID])
)
========

Suggest you do not use space (or other "illegal" characters) in Field names
....

 

[Van T. Dinh]

T1 and T2 are used as Aliases for the 2 Tables.

You note that I use the Table references numerous times in the Query/SQL and
if I don't use the short aliases, I need to type the long Table names in the
EXISTS clause. Using the Table Aliases shortens what I need to type and
reduces the chance of typing mistakes. Note that the QBE (Query Design
grid) can only handle a limited number of Query types and sooner or later,
typing directly into the SQL window will be necessary.

However, the main reason I use Aliases is for added clarity. An SQL String
with numerous long Table references just looks very messy and confuses me
....