• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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

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
Dsastray
Asked:
Dsastray
  • 22
  • 5
  • 4
1 Solution
 
DsastrayAuthor Commented:
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
 
PIER117Commented:
why are you deleting the records?
0
 
DsastrayAuthor Commented:
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
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.

 
DsastrayAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
which records do you want to delete?

using { EXISTS } is faster than using { IN }
0
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
So capricorn1, what would that SQL look like?
0
 
Rey Obrero (Capricorn1)Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
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
 
PIER117Commented:
That looks like overkill for some reason.
0
 
PIER117Commented:
Are you using Access 2000?
0
 
PIER117Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
Okay, hold on while I do that...
0
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
Copied tblAllData as a local table "tblAllData2". Retried the query with tblAllData2. Same errors.
0
 
Rey Obrero (Capricorn1)Commented:
try this

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

0
 
DsastrayAuthor Commented:
Same error.
0
 
Rey Obrero (Capricorn1)Commented:
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
 
PIER117Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
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
 
DsastrayAuthor Commented:
Thanks, brother.
0
 
DsastrayAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 22
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now