Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 05 May 2004 @ 20:41:55 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Deleting Duplicates with a single DELETE statement
From:   Maxwell, Donald

  An easier way is to create a temp table with  

     select all_columns
     from tab
     group by all_columns
     having count(*) > 1;

     delete from tab
     where (tab.all_columns)
     in (select all_columns from temp_tab);

     insert into tab
     sel * from temp_tab;

Looks like a good strategy, especially where the duplicates represent a small percentage of the whole table. The only thing I would be concerned with is the NULL handling, either using the in-list or using a join strategy for the delete. If all_columns contains many columns, some of which can be nullable, you need to have a good understanding of the demographics of the columns in order ot coalesce the NULL column to something that is not in the actual data, in order to perform the in-list or join processing for the delete.

Donald Maxwell

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019