?
Solved

Delete all records in one table that exist in another table

Posted on 2011-05-05
11
Medium Priority
?
444 Views
Last Modified: 2012-05-11
I have a table (call it big table) to which I add/apend data every month.  I'm now trying to go back and remove one of the months.  I've uploaded the original spreadsheet into a new table (call it small table).  I'd like to run a query that deletes all of the records in big table that exist in small table.  I'm trying a Delete Query but am having issues.  Does anyone have a better way to do this?
0
Comment
Question by:BBlu
  • 5
  • 3
  • 2
10 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 total points
ID: 35702107
Here is my guess ... and make a BACKUP first.

Example - change names accordingly:

DELETE Table1.*, Table1.ID
FROM Table1
WHERE (((Table1.ID) In (SELECT Table2.ID FROM Table2)));

mx
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 800 total points
ID: 35702133
try this:

delete from bigTable
where id in (select id from smalltable)
0
 

Author Closing Comment

by:BBlu
ID: 35702224
Perfect!  Thank you.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 35702265
BBlu
HainKurt posted the *Exact* same example I did ... 3 minutes later. Please hit the Request Attention button and select the answer I posted ... first.

mx
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35702354
sorry DatabaseMX, I just had a quick look to your query

DELETE Table1.*, Table1.ID
FROM Table1
WHERE (((Table1.ID) In (SELECT Table2.ID FROM Table2)));

and it seemed a bit strange to me (with lots of parentheses and especially the first line) and it looked like you used some sort of join (probably you used design tool and copied and pasted the generated code here) and I just typed the query...

at the end they are doing the same thing... but not exactly the same thing :)
0
 
LVL 75
ID: 35702381
JOIN ? No join.

I happened have the query designer open and put in the example and posted the SQL.  How are they not doing exactly the same thing?

mx


Capture1.gif
0
 

Author Comment

by:BBlu
ID: 35702686
Come on guys.  It is not that serious.  I certainly did not PURPOSELY choose one over the other.  I actually started typing the first one into my SQL editor (I usually do things using the designer, then if I have to, I look at the SQL code), but I was confused as to why I'd have two arguments after the delete, one with all (*) and one with the ID.  So the code seemed weird to me.  So, just on a whim I tried the second one because it looked cleaner and easier and I could understand what it was trying to accomplish.  And it worked.  Again, I didn't mean to slight you, DatabaseMX.  I can hit the request attention button and split the points. Would that be fair?
0
 
LVL 75
ID: 35702745
Here is the SQL of the first screen shot.  It's *exactly* what you would get using the Access query designer.  Exactly.  Try it.

If you paste

delete from bigTable
where id in (select id from smalltable)

into the SQL view of the query designer, then switch to design view ... you get what you see in the 2nd screenshot below ... which in fact to me, looks a bit confusing ... but does happen to work.

"Come on guys.  It is not that serious. "
Not sure what you mean?

My volunteered time is serious :-)

mx




Capture1.gif
Capture2.gif
0
 

Author Comment

by:BBlu
ID: 35709293
Perfect  Thanks, _alias99.
And thank you DatabaseMX and HainKurt.  I really meant no harm and hope to continue to get help from the both of you and everyone else in this great community in the future.
0
 
LVL 75
ID: 35709312
np
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question