Specify the table containg the records you want to delete

This it appears to be simple but getting all the stars to align on the SQL query is proving to be a pain.  

Basically, I have in Table1 a list all the IDs I want to delete out of Table2

The key field name in both tables is called ID.  What I want to do is delete all the records from Table2 that are equal to the ID in Table 1.

I have done this in a delete query with a join with the below code but it is giving be the above error

DELETE Tbl_ClientFlightControl.ID
FROM Tbl_ClientFlightControl INNER JOIN tbl_tmp_NotAsia_Clients ON Tbl_ClientFlightControl.ID = tbl_tmp_NotAsia_Clients.ID;

How do I fix the SQL code to get the desired results?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Try with:

FROM Tbl_ClientFlightControl 
WHERE ID IN (SELECT ID FROM tbl_tmp_NotAsia_Clients.ID);

Open in new window

mtrussellAuthor Commented:
It is returning an error code saying 'Could not find file H:\Data\tbl_tmp_NotAsia_Clients.ID'

Never seen anything like this before...  weird....
Gustav BrockCIOCommented:
Then, I guess, your table isn't linked properly.

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

mtrussellAuthor Commented:
Its a normal table.  It's not linked... not sure... let me play around with this some more...  It's just two normal tables in a database.  I'm not even sure why it is looking on the H drive.
Ryan ChongSoftware Team LeadCommented:
try this with minor amendment:

DELETE  FROM Tbl_ClientFlightControl WHERE ID IN (SELECT ID FROM tbl_tmp_NotAsia_Clients)
Gustav BrockCIOCommented:
Yes, it should read tbl_tmp_NotAsia_Clients, not tbl_tmp_NotAsia_Clients.ID

mtrussellAuthor Commented:
OK that works but now I have another issue... The ID is an Autonumber so when I create a temporary table it is taking the first number and creating an autonumber from there and doing a sequential numbering instead of just importing the ID number from Table 1...   (Access really shouldn't be this hard on something so simple...  :(

Anyway, I get the same error but how about if I skip the temporary table all together and just build it off a query...

What I want to do is delete all the records from Table 1 which are not part of a group.

DELETE Tbl_ClientFlightControl.*
FROM Tbl_ClientFlightControl LEFT JOIN Tbl_Regions ON Tbl_ClientFlightControl.Location = Tbl_Regions.Location
WHERE (((Tbl_Regions.Region) Not Like "*Americas*"));

Is there a simple way of doing this?
Gustav BrockCIOCommented:
Use the same syntax: ... where id in (select Id from YourQuery)

where YourQuery selects from the join you mention.

mtrussellAuthor Commented:
But what about all the not where Location = [x]

sorry for being so slow on all this but I've been hunting and pecking at a solution for a couple hours now.  I'm a bit in over my head on this and it looks like it should be pretty simple.

This what I have but the Not Like isn't right.  :(

DELETE  FROM Tbl_ClientFlightControl WHERE Location IN (SELECT Location FROM tbl_regions Not Like "*Americas*"));
Gustav BrockCIOCommented:
Then it could be:

DELETE  * FROM Tbl_ClientFlightControl 
WHERE Location IN (SELECT Location FROM tbl_regions WHERE Region Not Like "*Americas*")); 

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mtrussellAuthor Commented:
That worked....  thanks... one last one... I need to do the same but there is a table in the middle to get to the same result... could you point out how this one works as well?

DELETE Tbl_ClientCommunication.*
FROM (Tbl_ClientFlightControl RIGHT JOIN Tbl_ClientCommunication ON Tbl_ClientFlightControl.Primary_ID = Tbl_ClientCommunication.UCL) LEFT JOIN Tbl_Regions ON Tbl_ClientFlightControl.Location = Tbl_Regions.Location
WHERE (((Tbl_Regions.Region) Not Like "*America*"));

Really appreciate all your help on this...
Gustav BrockCIOCommented:
Create the query that lists the records to be deleted - perhaps using the query wizard.

Then insert the resulting SQL to replace the IN (...) above.

mtrussellAuthor Commented:
This isn't an unmatched query.  Basically, there is a field in Table 1 which links it to Table 2 which we did above.  What I am needing is to link a common identifier to Table 3.
mtrussellAuthor Commented:
The code I did it with is above.  I would have no idea how to reference table 3 in what you showed me or I would at least make an attempt at this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.