Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 03 Aug 2012 @ 01:42:32 GMT

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

Subj:   Re: Removing duplicates in Multiset table
From:   Clark, Dave


If the use of an external load utility is an issue, the select DISTINCT offered below will do the trick but, as pointed out in KA: S110009866E, this approach will not be efficient if the number of duplicate rows is small.

Another approach would be to create the new table, but only insert one copy of each row where there are duplicates in the current table. Then run a delete where the new table is used in a subquery to delete ALL occurrences of duplicate rows followed up with an insert/select from the new table to the current table which would insert just one copy of each row where there had been duplicates. It would work like this:

     ct tltest_dups(field1 integer,field2 integer);

     ins tltest_dups sel field1, field2 from tltest group by 1,2 having count(*) > 1;

     del tltest where (field1, field2) in (sel field1, field2 from tltest_dups );

     ins tltest sel field1, field2 from tltest_dups;

     drop table tltest_dups;


  <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: 28 Jun 2020