Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: DELETE recommendations?
From:   Dieter Noeth

Anomy Anom wrote:

  The sub query will result in (A,B,C1,1,2,3) and (A,B,C2,1,2,3)  

  And the delete part will delete both the rows.  

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:

  I want to delete only one row (any 1 one the resultant rows).  

     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


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023