Solved

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

Posted on 2008-10-18
31
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 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