<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="place"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";
color:black;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
pre
{margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:Arial;
color:maroon;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor=white lang=EN-US link=blue vlink=blue>
<div class=Section1><pre><font size=2 color=black face="Courier New"><span
style='font-size:10.0pt'>As far as I can see you don’t need to use a temp table. Just LEFT OUTER JOIN DocetNos to the Dockets table and delete from DocketNos where the Dockets table field is NULL. Eg something like:<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>DELETE FROM n<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>FROM DocketNos n<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>LEFT OUTER JOIN Dockets d ON (n.CoID = d.CoId) and (n.DocketNo = d.DocketNo)<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>WHERE d.CoID IS NULL <o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'> AND d.DocketNo IS NULL<o:p></o:p></span></font></pre>
<p class=MsoNormal><font size=2 color=maroon face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:maroon'><o:p> </o:p></span></font></p>
<pre><font size=2 color=black face="Courier New"><span style='font-size:10.0pt'>(Only one of the fields really needs to be tested for NULL in the WHERE clause as long as that field is declared NOT NULL(able) in the Dockets table. However if both fields are NULLABLE then this won’t work.) <o:p></o:p></span></font></pre>
<p class=MsoNormal><font size=2 color=maroon face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:maroon'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=maroon face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:maroon'>Cheers,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=maroon face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:maroon'>Paul.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=maroon face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:maroon'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=maroon face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:maroon'><o:p> </o:p></span></font></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
color=black face="Times New Roman"><span style='font-size:12.0pt;color:windowtext'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 color=black face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma;color:windowtext;font-weight:bold'>From:</span></font></b><font
size=2 color=black face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma;
color:windowtext'> delphi-bounces@ns3.123.co.nz
[mailto:delphi-bounces@ns3.123.co.nz] <b><span style='font-weight:bold'>On
Behalf Of </span></b>Kyley Harris<br>
<b><span style='font-weight:bold'>Sent:</span></b> Friday, 1 October 2004 4:12
p.m.<br>
<b><span style='font-weight:bold'>To:</span></b> NZ Borland Developers Group - <st1:place
w:st="on">Delphi</st1:place> List<br>
<b><span style='font-weight:bold'>Subject:</span></b> Re: [DUG] SQL Problem</span></font><font
color=black><span style='color:windowtext'><o:p></o:p></span></font></p>
</div>
<p class=MsoNormal><font size=3 color=black face="Times New Roman"><span
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 color=black face="Times New Roman"><span
style='font-size:12.0pt'>yep. using interbase, which doesn't have temp tables
is slightly harder to write the same code. <br>
SQL Server does have a few nice language features<br>
<br>
Stephen Bertram wrote: <o:p></o:p></span></font></p>
<pre wrap=""><font size=2 color=black face="Courier New"><span
style='font-size:10.0pt'>Again depending on the database it is often better to use a temporary<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>table and inner joins. e.g. for SQL Server:<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>CREATE TABLE #tmp (CoID chr(3), DocketNo int)<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>INSERT INTO #tmp -- Select all keys<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>SELECT CoID, DocketNo <o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>FROM DocketNos<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>DELETE FROM t -- This leaves only the keys to delete <o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>FROM #tmp t, Dockets d<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>WHERE t.CoID = d.CoId<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>AND t.DocketNo = d.DocketNo<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>DELETE FROM n -- Do the delete<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>FROM DocketNos n, #tmp t<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>WHERE n.CoID = t.CoId<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>AND n.DocketNo = t.DocketNo<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Doing this changed a job using NOT EXISTS from 3 days to 3 seconds. We<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>were working on tables with 20 million rows, but the same principle<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>applies.<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>HTH<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Stephen<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>-----Original Message-----<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>From: <a
href="mailto:delphi-bounces@ns3.123.co.nz">delphi-bounces@ns3.123.co.nz</a> [<a
href="mailto:delphi-bounces@ns3.123.co.nz">mailto:delphi-bounces@ns3.123.co.nz</a>]<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>On Behalf Of Kyley Harris<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Sent: Friday, 1 October 2004 2:10 p.m.<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>To: <a
href="mailto:mhoward@pslog.co.nz">mhoward@pslog.co.nz</a>; NZ Borland Developers Group - Delphi List<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Subject: Re: [DUG] SQL Problem<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Syntax may not be exactly right, depending on the database but<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>DELETE FROM DocketNos n where not Exist (select d.coid from Dockets d <o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>where d.CoID = n.coID and d.Dockeno = n.DocketNo)<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Mark Howard wrote:<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'> <o:p></o:p></span></font></pre>
<blockquote style='margin-top:5.0pt;margin-bottom:5.0pt' type=cite><pre wrap=""><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Hi<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>I'm wanting to delete rows from one table, DocketNos, where <o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>corresponding rows do not exist in a second table., Dockets.<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>The tables are linked via a composite key made up of<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>CoID Char(3) and<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>DocketNo Integer<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>I'm trying the following SQL (using DBISAM)<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Delete from DocketNos n where<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'> n.CoID||cast(n.DocketNo as Char(8)) not in<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'> (select d.CoID||cast(d.DocketNo as Char(8)) from Dockets d)<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>When I run this, I don't get any errors but it just goes away and <o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>never comes back to me.<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Can anyone see if there is a problem with this or is there maybe a <o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>better way to do it? There are perhaps 50,000 rows in<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>each table.<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>TIA<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Mark<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'> --<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Forest Production Systems Ltd<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>Creators of PSLog - A harvesting information system<o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><a
href="http://www.pslog.co.nz">www.pslog.co.nz</a><o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>_______________________________________________<o:p></o:p></span></font></pre><pre><st1:place
w:st="on"><font size=2 color=black face="Courier New"><span style='font-size:
10.0pt'>Delphi</span></font></st1:place> mailing list<o:p></o:p></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><a
href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a><o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><a
href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a><o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'> <o:p></o:p></span></font></pre></blockquote>
<pre wrap=""><font size=2 color=black face="Courier New"><span
style='font-size:10.0pt'>_______________________________________________<o:p></o:p></span></font></pre><pre><st1:place
w:st="on"><font size=2 color=black face="Courier New"><span style='font-size:
10.0pt'>Delphi</span></font></st1:place> mailing list<o:p></o:p></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><a
href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a><o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><a
href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a><o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'>_______________________________________________<o:p></o:p></span></font></pre><pre><st1:place
w:st="on"><font size=2 color=black face="Courier New"><span style='font-size:
10.0pt'>Delphi</span></font></st1:place> mailing list<o:p></o:p></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><a
href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a><o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><a
href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a><o:p></o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 color=black face="Courier New"><span style='font-size:10.0pt'> <o:p></o:p></span></font></pre></div>
</body>
</html>