Archives of the TeradataForum

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

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

Krishna-

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;

-dave.clark

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