Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 05 May 2004 @ 20:19:48 GMT

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

Subj:   Re: Deleting Duplicates with a single DELETE statement
From:   Dieter Noeth

Maxwell, Donald wrote:

  The use of RowID has been disabled in V2R5 going forward. V2R5.0 offers a GDO flag which can be set to enable RowID, but this flag is not enabled by default. V2R5.1 does not offer this backwards compatibility flag, ie. no use of RowID allowed.  

There's still Internal Field 58: ROWIDEnabled in V2R5.1, maybe it will be removed in future versions. But i can't believe it, RETRIEVE instead of SELECT is still working ;-)

     sel * from dbc.dbcinfo;

       *** Query completed. 2 rows found. 2 columns returned.
       *** Total elapsed time was 1 second.

     InfoKey                        InfoData
     ------------------------------ ---------------------------------
     RELEASE                        V2R.

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     sel rowid from dbc.tvm sample 1;

     sel rowid from dbc.tvm sample 1;

       *** Query completed. One row found. One column returned.
       *** Total elapsed time was 1 second.


  Without using RowID, one way to delete complete row duplicates in a multiset table is to insert-select into a new table, picking a winning row amongst duplicates using a RANK or CSUM function. Alternatively, you could insert-select into a set table (thereby eliminating duplicates). In either case, you would have to then delete your multiset table, and insert-select from your "non-duplicate" table back into your multiset table.  

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;


  <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