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.
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.
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;
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;
Microsoft AccessVBASQL
Last Comment
Daniel Pineault
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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.
Also, typically, if you setup your relationships properly with cascading deletions, when you delete the parent all the child entries are automatically deleted.