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.05.01.00.06
     VERSION                        05.01.00.06

       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.

     ROWID
     --------------------
     0000CF85FF2D00000100

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;

Dieter


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

Attachments

Library

Quick Reference