Link to home
Create AccountLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

How do I delete all rows in a table based on another table within Microsoft Access

How do I delete all rows in a table based on another table within Microsoft Access?
I'm trying some of the SQL statements online but they work mostly with SQL Server.

What's the proper syntax to get that working?
=============
I tried this using the Design Query first and then changing it to Delete - but it says could not delete from specified tables.

DELETE Jobs.*
FROM [DeleteThese] INNER JOIN [Jobs] ON DeleteThese.JobTicket = Jobs.JobTicket;

Open in new window

====================

I tried this directly in the SQL View (due to the online SQL searches) but it says specify the table containing the records you want to delete.

DELETE FROM DeleteThese INNER JOIN Jobs ON DeleteThese.JobTicket = Jobs.JobTicket;

Open in new window

Avatar of Daniel Pineault
Daniel Pineault

Could you explain the setup a little more and what you are trying to delete exactly?

Also, typically, if you setup your relationships properly with cascading deletions, when you delete the parent all the child entries are automatically deleted.
Avatar of stephenlecomptejr

ASKER

The first MS Access table (DeleteThese) has all the unique identifier's numbers that I want to delete from table: (Jobs) using the values found in JobTicket field.

There are no child entries tied to table:  Jobs
And DeleteThese is not related - just a created table telling the database which rows to delete.

There is a specific syntax that is written in MS Access SQL that will allow for this deletion.  I used to have the code syntax but its off in samples I can't access right now.

I could write a VBA script using DAO.Recordset that would loop through each record in table:  DeleteThese and delete such but I want the SQL syntax specifically (the one I put in Query Design) cause it's quicker to use in the future for other tables versus rewriting the code each time.

I also did a quick search for Microsoft Access SQL to do this particular deletion and nothing comes up except Microsoft SQL Server commands - thus the reason why I posted the question.
ASKER CERTIFIED SOLUTION
Avatar of Daniel Pineault
Daniel Pineault

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you Daniel, I appreciate it.  You the man.
I couldn't find that IN (SELECT syntax online).  Hopefully this helps other people as well searching for the same answer.
Glad I could help.