Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 06 May 2004 @ 15:08:18 GMT

  <Prev Next>   <<First <Prev

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

The RANK or CSUM would not be used in a DELETE statement, but rather in an insert-select to an empty table, eg.

     INSERT INTO nodupes_table ( all_columns )
     SELECT all_columns
     FROM dupes_table
     QUALIFY ...

where the QUALIFY ... can be (depending on your version):

     /* V2R5 syntax */
     QUALIFY ROW_NUMBER() OVER (PARTITION BY all_columns ORDER BY all_columns) = 1

     /* or V2R4 and higher equivalent functionality */
     GROUP BY all_columns
     QUALIFY CSUM(1, all_columns ) = 1

     /* or, alternative OLAP SUM V2R4 and higher syntax */
     QUALIFY SUM(1) OVER (PARTITION BY all_columns ORDER BY all_columns ROWS

I do think an insert-select into a set table would be a cleaner process (don't know about runtime, though):

     INSERT INTO nodupes_set_table ( all_columns )
     SELECT all_columns
     FROM    dupes_table;

Donald Maxwell

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