Simplify a SQL delete

[DIOS]

I have three tables and I want to cycle through all the IDs in table1 and
then
delet any records in table2 and table3 that do not have a matching ID in
table1.
I was going to do the following:

For each record in table1
pId = table1 ID
send a SQL = "DELETE * FROM table2 WHERE table2.ID <> pID
send a SQL = "DELETE * FROM table3 WHERE table3.ID <> pID
next

Does that sound like the most efficient way to do this? Is there a shorthand
way to do this?

Thanx
AGP

 

[John Vinson]

I have three tables and I want to cycle through all the IDs in table1 and
then delet any records in table2 and table3 that do not have a matching ID in
table1.

You're thinking procedurally. SQL works by Set operations, not
procedurally! For one thing you cannot "loop" in SQL, you must use
VBA.

I was going to do the following:

For each record in table1
pId = table1 ID
send a SQL = "DELETE * FROM table2 WHERE table2.ID <> pID
send a SQL = "DELETE * FROM table3 WHERE table3.ID <> pID
next

Does that sound like the most efficient way to do this?

Is there a shorthand way to do this?

Yes; a "Frustrated Outer Join" query:

DELETE Table2.*
FROM Table2 LEFT JOIN Table1
ON Table2.pID = Table1.pID
WHERE Table1.pID IS NULL;

and then the same for table3.

Two queries, no code, no looping!

 

[Tim Cali]

John, what do you mean by "Set operations"?

You're thinking procedurally. SQL works by Set operations, not
procedurally! For one thing you cannot "loop" in SQL, you must use
VBA.

 

[Michael Cole]

You're thinking procedurally. SQL works by Set operations, not
procedurally! For one thing you cannot "loop" in SQL, you must use
VBA.

And Tim Cali's message reminded me. You can loop in SQL via the use of
Cursors.

 

[Dale Fye]

You can loop, but why would you want to if you don't have to?

The biggest problem I have with my VB/ASP programmers is that they
think procedurally when dealing with data. Yes, there are some
occassions where you need to loop thru records, but in many instances
where you are doing deletes, inserts, and updates, you can execute one
query to accomplish what 1000 iterations thru a loop will do. This
can bring an application to a crawl.

I ran across one ASP application in my office which was designed for a
small set of data (150 people), but was being stretched to the limit
with 2500 people. The programmer wrote code to retrieve the name of
each user, then made another trip back to the server and queried
another table for each user, when he could have written a single SQL
statement in an aggregate query with the two tables joined. His
method took a minute for the page to build. When we rewrote it, it
barely took 10 seconds.

--
HTH

Dale Fye

You're thinking procedurally. SQL works by Set operations, not
procedurally! For one thing you cannot "loop" in SQL, you must use
VBA.

And Tim Cali's message reminded me. You can loop in SQL via the use
of
Cursors.

 

[John Vinson]

John, what do you mean by "Set operations"?

That needs a far longer answer than I can post here... see Codd and
Date, 1954 or thereabouts.

A "Set" is a mathematical construct, an unordered collection of
objects; most SQL operations are performed on a set of records.

 

[Michael Cole]

You can loop, but why would you want to if you don't have to?

The biggest problem I have with my VB/ASP programmers is that they
think procedurally when dealing with data. Yes, there are some
occassions where you need to loop thru records, but in many instances
where you are doing deletes, inserts, and updates, you can execute one
query to accomplish what 1000 iterations thru a loop will do. This
can bring an application to a crawl.

I ran across one ASP application in my office which was designed for a
small set of data (150 people), but was being stretched to the limit
with 2500 people. The programmer wrote code to retrieve the name of
each user, then made another trip back to the server and queried
another table for each user, when he could have written a single SQL
statement in an aggregate query with the two tables joined. His
method took a minute for the page to build. When we rewrote it, it
barely took 10 seconds.

[Sorry for not snipping, but I felt it deserved a further showing.]

I fully and totally agree. Cursors should only ever be used if there is no
other option, and like you, I have found some very bad SQL written. I was
certainly not advising the use of cursors for the question asked, merely
stating that there was such a thing.

 

[Jim Carlock]

You're thinking procedurally. SQL works by Set operations, not
procedurally! For one thing you cannot "loop" in SQL, you must use
VBA.

VBA? Visual BASIC for Applications? You totally lost me with that
statement.

I'm not sure I agree with thinking procedurally statement. Perhaps I've
been programming procedurally for too long and can't stop programming
in such a way.

But then again procedurally programming is common place and
necessary. And having the ability to see things from a top down
view (the hierarchical view), and the tree view representation, and
the recordset object, as well as the connection to the recordset...

And then there's the recursive views, the stack model (push and
pop), the structural view, objective oriented implementation...

But basically, when you come to think about it, the procedural
programming is the basis of it all. Without a well defined procedure
programming is a blob of ooze with no direction.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

 

[Michel Walsh]

Hi,

But your user would notice that it is much slower, at least with Jet (and probably on many
other db too). NOT EXISTS is rumoured to be poorly optimized in many implementations.


Vanderghast, Access MVP

 

[DIOS]

I am sorry for misrepresenting my question as I wrote the psesuo code
to convey what i wanted to do. You are correct, on my first test all records
in table 2 and table 3 were deleted if I used a loop. That was my first try
and it was late and I was not thinking properly. After looking at the
problem
I thought that there might be a cleaner single query execution that could
solve
the problem. Indeed the NOT EXISTS does a good job and is relatively quick
as I dont expect there to be a huge number of records in each table. Say
under
50,000 in each table. Was that the "clean" solution you were talking about?

Thanx
AGP

--

**********************************************************************
Unpak my email address before sending me personal email
**********************************************************************

Hi,

You don't want to "fill a combo box with the list of client", you want to ship a package where
the client address would be printed on it, as example. There is a world of difference between
procedural, where you specify the steps to perform, and with SQL, where you describe the ultimate
goal to reach (within a small universe). The real difference appears in that with the procedural
technique, you would end up coding just one case (one sequence of

execution ), even if other would

 

[Michel Walsh]

Hi,

The outer join with a test on the unpreserved table (that is the official "technical
expression"), which may sound a little bit snob, but which is, in reality, as simple as the
solution proposed by John Vinson, would probably behave quite well, for tables with more than 1000
rows. NOT EXISTS is optimized mainly only on very recent database engine, but if its running time
is fine on your machine (and on those of your client), it is more intuitive, definitively.
Maintenance is an important criteria, and more important than speed, in many cases.


Hoping it may help,
Vanderghast, Access MVP

 

[david epsom dot com dot au]

FWIW, I've seen 'NOT IS NULL' run like a slug -- and I've
seen 'NOT IN (subquery)' fall over on an ODBC connection.
At this point, I don't think there is a global optimum
for identifying missing records.

(david)

 

[Jim Carlock]

Jim,

Obviously, you will need to write procedural code loops to
load combo boxes and list boxes but getting the records one
at a time is inefficient and can cause bottlenecks in network
connections.

Dale, thanks for the reply.

You're statement brings up a situation that I've run into and I'm
totally procedurally oriented. I'm running an Excel spreadsheet
currently that goes through a recordset to fill in data based upon
a purchase order selected through a combobox I've placed on
the spreadsheet. I had initially loaded the spread sheet via MS
Query to get the records but I ran into problems when I tried
to display the spreadsheet on someone elses system.

So I've resorted to VBApp to fill the sheet. I run a for/next
loop inside the VBApp code for the spreadsheet that loops
through and clears the contents of certain cells, then it uses
DAO to fill the spreadsheet with the appropriate data. I have
to loop through the data to fill the spreadsheet with the data.

It's going through a purchase order and it's running considerably
slower than I'm used to seeing the code run. I can see the code
clearing the spreadsheet and then I can watch the code fill it
line by line. It only takes about a 3/4 of a second but that's how
slow it is. I think it might have something to with Excel translating
the VBApp code to machine code.

Wondering if you'd have done something similarly in the past
and perhaps have a suggestion.