<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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:
<blockquote
cite="mid18064CB1B6FE06429F79C5F43768C64F4CDDFC@aviemore.ensynergy.co.nz"
type="cite">
<pre wrap="">Again depending on the database it is often better to use a temporary
table and inner joins. e.g. for SQL Server:
CREATE TABLE #tmp (CoID chr(3), DocketNo int)
INSERT INTO #tmp -- Select all keys
SELECT CoID, DocketNo
FROM DocketNos
DELETE FROM t -- This leaves only the keys to delete
FROM #tmp t, Dockets d
WHERE t.CoID = d.CoId
AND t.DocketNo = d.DocketNo
DELETE FROM n -- Do the delete
FROM DocketNos n, #tmp t
WHERE n.CoID = t.CoId
AND n.DocketNo = t.DocketNo
Doing this changed a job using NOT EXISTS from 3 days to 3 seconds. We
were working on tables with 20 million rows, but the same principle
applies.
HTH
Stephen
-----Original Message-----
From: <a class="moz-txt-link-abbreviated" href="mailto:delphi-bounces@ns3.123.co.nz">delphi-bounces@ns3.123.co.nz</a> [<a class="moz-txt-link-freetext" href="mailto:delphi-bounces@ns3.123.co.nz">mailto:delphi-bounces@ns3.123.co.nz</a>]
On Behalf Of Kyley Harris
Sent: Friday, 1 October 2004 2:10 p.m.
To: <a class="moz-txt-link-abbreviated" href="mailto:mhoward@pslog.co.nz">mhoward@pslog.co.nz</a>; NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] SQL Problem
Syntax may not be exactly right, depending on the database but
DELETE FROM DocketNos n where not Exist (select d.coid from Dockets d
where d.CoID = n.coID and d.Dockeno = n.DocketNo)
Mark Howard wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi
I'm wanting to delete rows from one table, DocketNos, where
corresponding rows do not exist in a second table., Dockets.
The tables are linked via a composite key made up of
CoID Char(3) and
DocketNo Integer
I'm trying the following SQL (using DBISAM)
Delete from DocketNos n where
n.CoID||cast(n.DocketNo as Char(8)) not in
(select d.CoID||cast(d.DocketNo as Char(8)) from Dockets d)
When I run this, I don't get any errors but it just goes away and
never comes back to me.
Can anyone see if there is a problem with this or is there maybe a
better way to do it? There are perhaps 50,000 rows in
each table.
TIA
Mark
--
Forest Production Systems Ltd
Creators of PSLog - A harvesting information system
<a class="moz-txt-link-abbreviated" href="http://www.pslog.co.nz">www.pslog.co.nz</a>
_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
</pre>
</blockquote>
<pre wrap=""><!---->_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
</pre>
</blockquote>
</body>
</html>