Re: [DUG] SQL Problem

[Kyley Harris]

yep. using interbase, which doesn't have temp tables is slightly harder to write the same code. SQL Server does have a few nice language features Stephen Bertram wrote: 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

_______________________________________________
Delphi mailing list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi