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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kyley Harris Sent: Friday, 1 October 2004 2:10 p.m. To: [EMAIL PROTECTED]; 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: > 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 > www.pslog.co.nz > _______________________________________________ > Delphi mailing list > [EMAIL PROTECTED] > http://ns3.123.co.nz/mailman/listinfo/delphi > _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi
