Archives of the TeradataForum
Message Posted: Fri, 20 Aug 2004 @ 10:01:48 GMT
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
delete from tab where (pk1, p2, pk3) not in (select pk1,pk2, min(pk3) from tab group by 1,2 )
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|