Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1

    Thumbs up Answered: Delete records not in another table

    I know this has been answered several times in other inquiries. I have a little different situation. I have a table that has multiple log entries (3 to 1) for each record in a main table. Not only that but the log file holds multiple file types. These tables are large millions of records each. One of the main file has 62mil the log will have 180mil. I need to delete records from the main table that don't have matches in the log file. My current query is below. I have read many posts that say don't use NOT IN. So I am trying to find the best way to do this delete.

    Code:
    ELETE FROM CT1_Trace_Data
    WHERE TR_SerNum NOT IN
    	(
    	SELECT DISTINCT TR_SerNum 
    	FROM Trace_Return_Table 
    	WHERE FileType = 'CT1' 
    	)
    My guess is to use something like this, but I'm no expert. When it comes to big data tables every millisecond counts. The current query runs around 2 hours.
    Code:
    IF OBJECT_ID('tempdb.dbo.#TempTable') IS NOT NULL
        DROP TABLE #TempTable ;
    
    SELECT * INTO #TempTable 
    FROM
    	(
    	SELECT DISTINCT TR_SerNum 
    	FROM Trace_Return_Table 
    	WHERE FileType = 'CT1' 
    	)
    
    DELETE CT1_Trace_Data
    FROM CT1_Trace_Data a RIGHT JOIN #TempTable b 
    ON a.TR_SerNum  = b.TR_SerNum 
    WHERE a.SerNum IS NULL
    Last edited by wjburke2; 08-24-16 at 14:08.

  2. Best Answer
    Posted by spacebar

    "Check out this article:http://sqlperformance.com/2012/12/t-...anti-semi-join

    Code:
    delete
     from  ct1_trace_data ctd
    where  not exists ( select null
                         from  trace_return_table 
                        where  tr_sernum = ctd.tr_sernum
                         and   filetype  = 'CT1' )
    "


  3. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Check out this article:http://sqlperformance.com/2012/12/t-...anti-semi-join

    Code:
    delete
     from  ct1_trace_data ctd
    where  not exists ( select null
                         from  trace_return_table 
                        where  tr_sernum = ctd.tr_sernum
                         and   filetype  = 'CT1' )

  4. #3
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    Thanks, I always like answers that have good research behind them. I will give this a try.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •