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

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023