By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,685 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

delete query for unmatched records

P: n/a
I have two tables A & B and I want to delete all the records in A that
are not in B. Can I do this in the query builder? It seems like a
simple problem.

I can easily find the records in A that aren't in B by using the query
wizard to come up with a query "A without matching B".

I added table A to the query builder and Query "A without matching B"
joined them with the index field (Loan Number) made the query a delete
query and
chose Table A.* in the grid. I am getting the error "Couldn't delete
from specified tables". It can't be a permission problem because I
have full control on the database.

The SQL view is this:

DELETE A.*
FROM A INNER JOIN [A Without Matching B] ON A.[Loan Number] = [A
Without Matching B].[Loan Number];

I could go through the table programatically and delete all loans in A
that come up in the query "A without matching B" but this seems very
time consuming.
Shouldn't there be a way to do this in the query builder or at least
with a written SQL statement?

Joe Del Medico jo*********@yahoo.com
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
this should do it

DELETE *
FROM tblA
WHERE (((tblA.loanNumber) Not In (SELECT loanNumber from tblB)));
jo*********@yahoo.com (Joe Del Medico) wrote in message news:<29**************************@posting.google. com>...
I have two tables A & B and I want to delete all the records in A that
are not in B. Can I do this in the query builder? It seems like a
simple problem.

I can easily find the records in A that aren't in B by using the query
wizard to come up with a query "A without matching B".

I added table A to the query builder and Query "A without matching B"
joined them with the index field (Loan Number) made the query a delete
query and
chose Table A.* in the grid. I am getting the error "Couldn't delete
from specified tables". It can't be a permission problem because I
have full control on the database.

The SQL view is this:

DELETE A.*
FROM A INNER JOIN [A Without Matching B] ON A.[Loan Number] = [A
Without Matching B].[Loan Number];

I could go through the table programatically and delete all loans in A
that come up in the query "A without matching B" but this seems very
time consuming.
Shouldn't there be a way to do this in the query builder or at least
with a written SQL statement?

Joe Del Medico jo*********@yahoo.com

Nov 12 '05 #2

P: n/a
Thanks Roger !
Joe

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.