Archives of the TeradataForum

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

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

Anomy Anom wrote:

The query does what you wrote in your first mail:

"I need to delete the records from the table which have matching PK1 and PK2 but different PK3. eg - first 2 records in the above list."

And now for something completely different:

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

And if there are more than 2 rows with equal pk1/pk2 this will delete only one row.

If you want to delete all but one row:

Run that query repeatedly until activity_count = 0

or

     delete from tab
     where (pk1, p2, pk3) not in
     (select pk1,pk2, min(pk3)
       from tab
       group by 1,2
     )

Dieter

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