It looks like I am going to be able to do something like that in DBISAM too.
Will have a go when I'm back on deck next week.
Thanks
Mark
On Fri, 01 Oct 2004 16:11:57 +1200, Kyley Harris <[EMAIL PROTECTED]> wrote:
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
-- 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
