RE: [DUG] SQL Problem

Thu, 30 Sep 2004 21:28:45 -0700 

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:
 
 
DELETE FROM n
FROM DocketNos n
LEFT OUTER JOIN Dockets d ON (n.CoID = d.CoId) and (n.DocketNo = d.DocketNo)
WHERE d.CoID IS NULL 
  AND d.DocketNo IS NULL


 



(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.) 



 



Cheers,



Paul.



 



 















From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of Kyley Harris

Sent: Friday, 1 October 2004 4:12
p.m.

To: NZ Borland Developers Group - Delphi List

Subject: Re: [DUG] SQL Problem






 



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

Reply via email to