Access Delete Query

I'm trying to use the following code to delete records in one table based on another table and get an error message :specifiy the table containing the records you want to delete.


DELETE tbl_Expiriation.*, tbl_MOU.[System Short Name]
FROM tbl_MOU RIGHT JOIN tbl_Expiriation ON tbl_MOU.[System Short Name] = tbl_Expiriation.[System Short Name]
WHERE (((tbl_MOU.[System Short Name]) Is Null));
shieldscoAsked:
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.

MlandaTCommented:
The problem is that you have specified multiple tables after your delete. You can only specify one table to delete from. If you want to delete from multiple tables, you have to issue two separate delete statements or better still, use relationships and foreign keys to cascade the deletes.
DELETE tbl_Expiriation.*
FROM tbl_MOU RIGHT JOIN tbl_Expiriation ON tbl_MOU.[System Short Name] = tbl_Expiriation.[System Short Name]
WHERE (((tbl_MOU.[System Short Name]) Is Null));

Open in new window

0
shieldscoAuthor Commented:
So what would the code look like
0
MlandaTCommented:
Please try the snippet I posted
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

shieldscoAuthor Commented:
When I used this code I get a syntax error:

delete *
 FROM tbl_Expiriation
 where   RIGHT JOIN tbl_Expiriation ON tbl_MOU.[System Short Name] = tbl_Expiriation.[System Short Name] In (select  tbl_MOU.[System Short Name] from tbl_MOU where  tbl_MOU.[System Short Name]Is Null) ;
0
MlandaTCommented:
I'm sorry. But could you please try the snippet I posted here?
0
shieldscoAuthor Commented:
I get the original error when using your code
0
MlandaTCommented:
The query syntax I posted seems to be correct in itself... BUT it turns out that Access won't delete from a join. (NOTE: I've seen other people referring to this "fact", but not on the Microsoft Office website).

Option 1: Use an EXISTS statement (or an IN)
DELETE 
FROM tbl_Expiring
WHERE Exists (SELECT * FROM tbl_MOU WHERE tbl_Expiring.[System Short Name] = tbl_MOU.[System Short Name]);

Open in new window

However, your filter is not making a lot of sense to me. You can linking your two tables on [System Short Name] BUT you have a condition for one of these values (tbl_MOU.[System Short Name]) to be null... which (apart from being impossible to say 'WHERE NULL = NULL') makes it almost the same as just a DELETE FROM tblExpiring WHERE [System Short Name] IS NULL

Option 2: Update first, then delete
A useful workaround is to flag the records to be deleted using an Update query by putting an out of scope value into one of the fields of the records to be deleted then use that flag in the Delete query.
0
shieldscoAuthor Commented:
When I use a select statement the query works.
0
shieldscoAuthor Commented:
Your option 1 will delete everything... that is not what I want. I want to delete records that are not in both tables.
0
shieldscoAuthor Commented:
Option 2 is not an option for me.
0
MlandaTCommented:
I know. I was so convinced the SQL I gave you would work, that when you mentioned it didn't... I created a sample Access database. And when it failed, I took to googling it on Bing :)
My-New-App.accdb
0
MlandaTCommented:
I want to delete records that are not in both tables.
Do it in 2 steps
DELETE 
FROM tbl_Expiring
WHERE NOT Exists (SELECT * FROM tbl_MOU WHERE tbl_Expiring.[System Short Name] = tbl_MOU.[System Short Name]);

DELETE 
FROM tbl_MOU
WHERE NOT Exists (SELECT * FROM tbl_Expiring WHERE tbl_Expiring.[System Short Name] = tbl_MOU.[System Short Name]);

Open in new window

0
shieldscoAuthor Commented:
Ok I will close out and re-ask
0
shieldscoAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for shieldsco's comment #a40984882

for the following reason:

Expert could not solve
0
MlandaTCommented:
Look for section Delete Query Fails to Run on page... http://www.fmsinc.com/microsoftaccess/query/snytax/delete-query.html
0
MlandaTCommented:
Could not solve???

1 - I pointed out that the intended DELETE command is unsupported in MS Access (including an example of an Access database showing that the required DELETE was not possible)
2 - I provided alternative implementation options for the required DELETE (including sample code)
0
shieldscoAuthor Commented:
None of the solutions that the expert provided worked including the link to http://www.fmsinc.com/microsoftaccess/query/snytax/delete-query.html
0
IrogSintaCommented:
code to delete records in one table based on another table
I don't quite understand what you're trying to do.  Can you explain this a bit more.  Are you deleting only from 1 table?  If so, which one?  Are you deleting records in one table that exists in both tables?  Or are you deleting records in a table that don't exist in another table?

Ron
0
shieldscoAuthor Commented:
I'm trying to delete the unmatched records in the tbl_Expiration based on the tbl_MOU. If the record is not in the tbl_MOU and it's in the tbl_Expiration then delete it.....
0
shieldscoAuthor Commented:
The following code works in the view mode however when I try to run an error occurs:

DELETE tbl_Expiriation.*, tbl_MOU.[System Short Name]
FROM tbl_MOU RIGHT JOIN tbl_Expiriation ON tbl_MOU.[System Short Name] = tbl_Expiriation.[System Short Name]
WHERE (((tbl_MOU.[System Short Name]) Is Null));
0
MlandaTCommented:
Yes... It works in view mode. But Access will not allow you to delete that way. If we accept that, then we'd open up to accepting other solutions or approaches that will accomplish what you want here. Access doesn't really like deleting based on joins. There are cases where it will work, as discussed in the article I sent you yesterday. I even sent you a DELETE statement which will delete records that do not appear in the other table. It's 24hrs since I first helped with this. It's really a simple thing, but you just need to approach it differently. I'm not sure you'd accomplish much by trying to force access to do what it quite clearly doesn't allow.
0
shieldscoAuthor Commented:
I figured it out... this is the code
DELETE tbl_Expiriation.[System Short Name], tbl_Expiriation.[System Short Name], *
FROM tbl_Expiriation
WHERE (((tbl_Expiriation.[System Short Name]) In (select  tbl_MOU.[System Short Name] from tbl_MOU where  tbl_MOU.[System Short Name]) And (tbl_Expiriation.[System Short Name]) In (select  tbl_MOU.[System Short Name] from tbl_MOU where  tbl_MOU.[System Short Name] <> [tbl_Expiriation].[System Short Name])));
0

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
IrogSintaCommented:
Looks to me that the only thing wrong with your original SQL is that you used RIGHT JOIN instead of LEFT JOIN.  The following should work:
 
DELETE tbl_Expiriation.*, [tbl_MOU].[System Short Name]
FROM tbl_Expiriation
LEFT JOIN tbl_MOU ON tbl_Expiriation.[System Short Name]= tbl_MOU.[System Short Name]
WHERE tbl_MOU.[System Short Name] Is Null;

Ron
0
shieldscoAuthor Commented:
Access will not let you use joins in delete queries. Same error message
1
MlandaTCommented:
You are right. The best way to structure DELETE queries in Access is to use the IN / EXISTS clause and avoid the JOIN.
0
IrogSintaCommented:
Yes, you can actually use Joins in Delete queries in Access.  I do it all the time.  I even did one to simulate your tables and it worked fine.  If you don't believe me, here's a link to FMS that explains Delete queries and you'll notice in the Syntax presented that it includes the Join option.  You must have something else going on with your tables.  If you'd like, you can post a sample database with your two tables and we can take a look at it.  Just be sure there is no private data in the tables.

Ron
0
MlandaTCommented:
@Irog you are probably right. We also reviewed some online material that said it's possible, but under certain conditions... Primary keys, one-one relationships and so on. So in the end, the quickest solution and recommendation (without going into the specifics of the database design) was  to just recommend to use the DELETE...WHERE...IN structure
0
aikimarkCommented:
I thought this version of Ron's SQL would have worked.  Has it been tried?
DELETE tbl_Expiriation.*
FROM tbl_Expiriation 
LEFT JOIN tbl_MOU ON tbl_Expiriation.[System Short Name]= tbl_MOU.[System Short Name]
WHERE tbl_MOU.[System Short Name] Is Null;

Open in new window

0
IrogSintaCommented:
If you are getting an error message with the SQL I posted, it's more than likely that one or more fields used in your links is not a primary key.  If that's the case, you need to use the DISTINCTROW syntax to let Access know that the relationship between the two tables is one to one.  Your SQL would then look like this:

 DELETE DISTINCTROWN tbl_Expiriation.*, [tbl_MOU].[System Short Name]
 FROM tbl_Expiriation
 LEFT JOIN tbl_MOU ON tbl_Expiriation.[System Short Name]= tbl_MOU.[System Short Name]
 WHERE tbl_MOU.[System Short Name] Is Null;

This property can also be set in the queries property sheet:
Capture.PNG
0
PatHartmanCommented:
Access doesn't really like deleting based on joins
Deleting records using a join is possible but will only delete the lowest level "child" records.  It will not delete "parent" records AND you may only include one table name in the Delete clause.  If you want to use criteria based on "child" tables to delete from "parent" tables, then you must use a subquery.  I believe that someone posted that syntax.
0
shieldscoAuthor Commented:
I get the same error message when I use distinctrow. :specifiy the table containing the records you want to delete.


DELETE DISTINCTROW tbl_Expiriation.*, tbl_MOU.[System Short Name]
FROM tbl_Expiriation LEFT JOIN tbl_MOU ON tbl_Expiriation.[System Short Name] = tbl_MOU.[System Short Name]
WHERE (((tbl_MOU.[System Short Name]) Is Null));
0
MlandaTCommented:
@shieldsco .... I thought you have eventually found a working solution... with the DELETE FROM table WHERE table.field IN (subquery) ?
0
PatHartmanCommented:
I didn't mean to interject myself into the conversation.  I believe that others have already posted the correct solution but you don't seem to be using it.  You also still have two table names in the delete clause.

Please tell us
1. the primary keys of each table.
2. which table you want to delete from.  You can't delete from both.  In a join, you can ONLY delete from the lowest level many-side table.

It looks like you want to delete from tbl_Expiriation and that looks like it should be the 1-side of the relationship.  I think you are trying to delete rows from tbl_Expiration where there are no matching rows in tbl_Mou.  In that case, you MUST use the subquery suggested earlier by MlandaT.  Have you ever tried that?  To delete from both tables, you need the TWO queries that he suggested.  Each has a subselect that refers to the other table.
0
shieldscoAuthor Commented:
Pat - I figured it out two days ago... Please refer to my posts
0
PatHartmanCommented:
But you haven't closed the thread and you are still asking questions.  If you have an answer, please close the thread and tell us what you settled on so others can benefit.
1
shieldscoAuthor Commented:
No expert provided me with a solution that worked for me.. see my posts
0
shieldscoAuthor Commented:
I followed the proper procedures by accepting my comments as the solution.
0
shieldscoAuthor Commented:
thermoduric - you award the points... I do not have any more time for this question. Thanks
0
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
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.