Archives of the TeradataForum

Message Posted: Fri, 20 Aug 2004 @ 10:36:48 GMT

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

Dieter,

there is one problem with this

     delete from tab
     where (pk1, p2, pk3) in
     (select pk1,pk2, max(pk3)
       from tab
       group by 1,2
       having min(pk3) <> max(pk3)
     )

solution. It does not delete rows where the PK is the same, but the later attributes differ.

A subquery with qualified Rank could do the job to identify exactly one row to be kept - but the makers at NCR did not see fit to allow subqueries the use of Rank ;)

To get around this problem one could

1. create an empty copy of the original table.

2. Populate new table with only data to keep

3. Drop old table and rename new table to old tablename (empty old table populate with data from new table)

This might also have performance advantages. (Insert into empty compared to delete). But one needs the right rights and the space, etc.

Be that as it may. The interesting part would be inserting into the new table.

     Insert into NEW_EMPTY_TABLE
     Select * from
     OLD_TABLE
     Group by PK1, PK2
     Qualify Rank
     (PK3, all other columns of the table) = 1
     ;

Assumed OLD_TABLE is identified as a SET table one should end with exactly one record for each PK1, PK2 combination.

There are other solution to accomplish the same, but I personally like the use of RANK (I think it improves readability) and also the insert into empty tables.

Thanks!

Mirjam

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