Solved

How do I delete records based on matching records in a subquery? MS Access

Posted on 2008-10-18
31
281 Views
Last Modified: 2012-05-05
I want to delete any records from a table (tblAllData) before I append new records from qryNewData. I don't know how to do this. Subqueries are a mystery.

Here is my SQL

DELETE tblAllData.*
FROM tblAllData
WHERE EXISTS
(SELECT qryNewData.SchoolNum, qryNewData.ReportType, qryNewData.ShortVarCode, qryNewData.Year
FROM qryNewData
WHERE tblAllData.SchoolNum = qryNewData.SchoolNum AND tblAllData.ReportType =qryNewData.ReportType AND tblAllData.ShortVarCode = qryNewData.ShortVarCode AND tblAllData.Year = qryNewData.Year);

Unfortunately, this returns ALL records from tblAllData, If I execute it I would be deleting everything, which is not my intent.

How in the devil do I accomplish this?
0
Comment
Question by:Dsastray
  • 22
  • 5
  • 4
31 Comments
 

Author Comment

by:Dsastray
ID: 22749599
DELETE tblAllData.*, tblAllData.SchoolNum, tblAllData.ReportType, tblAllData.ShortVarCode, tblAllData.Year
FROM tblAllData
WHERE (((tblAllData.SchoolNum) In (select SchoolNum from qryNewData)) AND ((tblAllData.ReportType) In (select ReportType from qryNewData)) AND ((tblAllData.ShortVarCode) In (select ShortVarCode from qryNewData)) AND ((tblAllData.Year) In (select Year from qryNewData)));
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22749600
why are you deleting the records?
0
 

Author Comment

by:Dsastray
ID: 22749614
The above works.

But my first syntax was damn near straight cribbed from Alan Brownes subquery tutorial. What was the first one doing?

And is there a more efficient way of accomplishing this? (Is my solution optimal?) I worry that a sub-optimal solution could cost me a lot of time. This is one of the few times that I work with a DB that has Millions of records, so I get concerned...
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:Dsastray
ID: 22749635
Since I cannot overwrite data when I append to tblAllData, my understanding is I can either
a) first delete from tblAllData any records matching those in qryNewData, then append qryNewData to tblAllData
b) update any records in tblAllData matching those in qryNewData, then append qryNewData to tblAllData

I am choosing a)

Is b) preferred?

Is there a c) I don't know about?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22749652
which records do you want to delete?

using { EXISTS } is faster than using { IN }
0
 

Author Comment

by:Dsastray
ID: 22749679
I should say
b) update any records in tblAllData matching those in qryNewData (updating fields in tblAllData from with same fields qryNewData), then append qryNewData to tblAllData (to account for new records)
0
 

Author Comment

by:Dsastray
ID: 22749682
So capricorn1, what would that SQL look like?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22749684
1. if you want newdata and don't want to keep record with old data
    a. how many fields will have new values?
        if most of the fields will have new data
              * delete old record then
              * append new record
        if only few fields will have new data
              * update the record is a good way to do it


     
0
 

Author Comment

by:Dsastray
ID: 22749686
Because obviously I know not how to use EXISTS, or the first SQL I posted would be working and I'd be a happy happy man.
0
 

Author Comment

by:Dsastray
ID: 22749695
I'd like to know how to do both approaches, ultimately, but why don't you explain the 1a, and then I'll post a question asking you to explain how to do 1b.
0
 

Author Comment

by:Dsastray
ID: 22749816
And, I just realized, my solution query I posted above is sub optimal. As in it doesn't work right at all.

I was hoping to delete as if I had INNER joins between tblAllData and qryNewData on Year, SchoolNum, ShortVarCode, ReportType, but this is not doing that.

Ugh. This works (but is slow):
DELETE tblAllData.*, tblAllData.SchoolNum, tblAllData.ReportType, tblAllData.ShortVarCode, tblAllData.Year
FROM tblAllData
WHERE (((tblAllData.SchoolNum) In (SELECT qryNewData.SchoolNum
FROM tblAllData INNER JOIN qryNewData ON (qryNewData.Year = tblAllData.Year) AND (tblAllData.ShortVarCode = qryNewData.ShortVarCode) AND (tblAllData.ReportType = qryNewData.ReportType) AND (tblAllData.SchoolNum = qryNewData.SchoolNum))) AND ((tblAllData.ReportType) In (SELECT qryNewData.ReportType
FROM tblAllData INNER JOIN qryNewData ON (qryNewData.Year = tblAllData.Year) AND (tblAllData.ShortVarCode = qryNewData.ShortVarCode) AND (tblAllData.ReportType = qryNewData.ReportType) AND (tblAllData.SchoolNum = qryNewData.SchoolNum))) AND ((tblAllData.ShortVarCode) In (SELECT qryNewData.ShortVarCode
FROM tblAllData INNER JOIN qryNewData ON (qryNewData.Year = tblAllData.Year) AND (tblAllData.ShortVarCode = qryNewData.ShortVarCode) AND (tblAllData.ReportType = qryNewData.ReportType) AND (tblAllData.SchoolNum = qryNewData.SchoolNum))) AND ((tblAllData.Year) In (SELECT qryNewData.Year
FROM tblAllData INNER JOIN qryNewData ON (qryNewData.Year = tblAllData.Year) AND (tblAllData.ShortVarCode = qryNewData.ShortVarCode) AND (tblAllData.ReportType = qryNewData.ReportType) AND (tblAllData.SchoolNum = qryNewData.SchoolNum))));
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22749892
That looks like overkill for some reason.
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22749905
Are you using Access 2000?
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22749917
Ok, try the first query you posted again.  But first copy the select statement from the exists clause and see if that works and returns the records you want.
0
 

Author Comment

by:Dsastray
ID: 22749930
This is elegant, but does not work:

SELECT tblAllData.*
FROM tblAllData;
WHERE EXISTS
  (select *
    from qxtbNewType
    where qxtbNewType.SchoolNum = tblAllData.SchoolNum);

I get the error message "The Microsoft Jet database engine does not recognize tblAllData.SchoolNum as a valid field name or expression."
0
 

Author Comment

by:Dsastray
ID: 22749935
Okay, hold on while I do that...
0
 

Author Comment

by:Dsastray
ID: 22749959
So if you mean does this:

SELECT  qryNewData.SchoolNum,  qryNewData.ReportType,  qryNewData.ShortVarCode,  qryNewData.Year
FROM  qryNewData

work? Yes. It returns the records I want.

But if I copy

SELECT  qryNewData.SchoolNum,  qryNewData.ReportType,  qryNewData.ShortVarCode,  qryNewData.Year
FROM  qryNewData
WHERE tblAllData.SchoolNum =  qryNewData.SchoolNum AND tblAllData.ReportType = qryNewData.ReportType AND tblAllData.ShortVarCode =  qryNewData.ShortVarCode AND tblAllData.Year =  qryNewData.Year

I get the same message as above; "The Microsoft Jet database engine does not recognize tblAllData.SchoolNum as a valid field name or expression."

(And note, if I delete tblAllData.SchoolNum, I get "The Microsoft Jet database engine does not recognize tblAllData.ReportType as a valid field name or expression." and so forth ~ no misspellings)
0
 

Author Comment

by:Dsastray
ID: 22749972
Sorry, missed your q. I am using 2002, but realize that the DB is in 2000 format. Is there a gotcha here? (meanwhile, I'll be converting it to 2002 and trying again).
0
 

Author Comment

by:Dsastray
ID: 22749983
No difference, I am still getting "The Microsoft Jet database engine does not recognize tblAllData.SchoolNum as a valid field name or expression." in my new 2002 format db. when I use

SELECT tblAllData.*
FROM tblAllData;
WHERE EXISTS
  (select *
    from qryNewData
    where qryNewData.SchoolNum = tblAllData.SchoolNum);
0
 

Author Comment

by:Dsastray
ID: 22750022
Okay, I'll feel dumb if this is an issue, because I like to try and give full disclosure:

tblAllData is a linked table.

Umm, let's see, anything else?

Access 2002, SP3
0
 

Author Comment

by:Dsastray
ID: 22750038
Copied tblAllData as a local table "tblAllData2". Retried the query with tblAllData2. Same errors.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22750134
try this

SELECT tblAllData.*
FROM tblAllData
WHERE (EXISTS
  (select SchoolNum
    from qryNewData
    where qryNewData.SchoolNum = tblAllData.SchoolNum)=true);

0
 

Author Comment

by:Dsastray
ID: 22750164
Same error.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22750325
Dsastray,

create  a NEW query.
copy and paste this

SELECT tblAllData.*
FROM tblAllData
WHERE (EXISTS(select SchoolNum from qryNewData where qryNewData.SchoolNum = tblAllData.SchoolNum)=true);

in the SQL view
0
 
LVL 1

Expert Comment

by:PIER117
ID: 22750393
Does this return what you are looking for?  You may have to tweak the field names


SELECT tblAllData.*, tblAllData.SchoolNum
FROM tblAllData
WHERE (((tblAllData.SchoolNum) In (Select schoolnum from qryNewData)));

Open in new window

0
 

Author Comment

by:Dsastray
ID: 22750483
Capricorn - Okay, so I tweaked it, because I am actually trying to match on several fields, I had just simplified it.

Delete tblAlldata.*
From tblAllData
WHERE (Exists (SELECT SchoolNum, ReportType, ShortVarCode,Year From qryNewData WHERE (qryNewData.SchoolNum = tblAllData.SchoolNum) AND (qryNewData.ReportType = tblAllData.ReportType) AND (qryNewData.ShortVarCode = tblAllData.ShortVarCode) And (qryNewData.Year = tblAllData.Year)))

Damn. It works!

I made far too many changes to say exactly what combination made the magic happen, but some notes:

(I'm mortified to admit I didn't have one before but) I went in and added a primary key to tblAllData. It had a unique index that was a combination of SchoolNum, ReportType, Year & ShortVarCode. I made a primary key out of the same combination.

Not sure how this affected things. A last interesting note, I made a quick table, indexed, primary keys etc. that contains the same data as qryNewData. (tblNewData). It runs the same EXISTS query about 10 times faster. (Damn. It works, and it is fast!)
0
 

Author Comment

by:Dsastray
ID: 22750488
Anyhow, PIER117, I'll ask a separate question asking how do to this with an IN statement (because I am genuinely curious), if you can figure out how to use multiple criteria that way... My syntax in post 22749816 is essentially the same as yours above (when matching more than one field), no?

Yet it would return multiple combinations -
ie, if qryNewData has
schoolnum          year
1                          2008
2                          2006

using separate IN statements returns

schoolnum          year
1                          2006
1                          2008
2                          2006
2                          2008

0
 

Author Comment

by:Dsastray
ID: 22750498
Also, what is weird, and I think the genius of Capricorn1's solution

SELECT tblAllData.*
FROM tblAllData
WHERE ((Exists (SELECT tblNewData.* From tblNewData WHERE (tblNewData.SchoolNum = tblAllData.SchoolNum) AND (tblNewData.ReportType = tblAllData.ReportType) AND (tblNewData.ShortVarCode = tblAllData.ShortVarCode) And (tblNewData.Year = tblAllData.Year)))=True);

works, but takes FOREVER!!! Apparently the ...Exists(Select tblNewData.*  <----- is a bad idea
0
 

Author Comment

by:Dsastray
ID: 22750508
Meaning (and I am frankly not inclined to test this theory out, because I've already pissed my day away on this)

"The Microsoft Jet database engine does not recognize tblAllData.SchoolNum as a valid field name or expression."

error was I believe related to having a (linked?!?) table, with no primary key, and then the rest of it was the tweak of replacing SELECT qryNewData.* with the actual field names.

Oh, and a temporary table beats a query.

Phew
0
 

Author Closing Comment

by:Dsastray
ID: 31507466
Thanks, brother.
0
 

Author Comment

by:Dsastray
ID: 22750514
So all to Capricorn1, with a distinct possibility that if you can make me a working IN version, PIER117, I'll ask that question and you can whisk me off my feet.

I'd like to see you get your due, but if you aren't able to come up with a solution that works, I probably won't post the new Q. I'm curious and grateful, but not THAT curious to start the chase again with a whole bunch of other Experts.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

776 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