Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 09 Sep 2004 @ 12:57:22 GMT


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


Subj:   Re: Deleting duplicate rows in Table without INS/SEL
 
From:   teradatanewsgroup

OK - when you start think to the end - SP below works without alter table but still deletes only based on given ROW info...

I was trying to define a cursor with order by (as this would enable to delete without row info) but this seems not to work. Need to check this in the manual.

Again - I would expect poor to horrible performance.

Personally I thing -

1. Insert/select with group by into a new table in case of many duplicates


or

2. Insert/select with group by and having count(*) > 1 into TMP table.

3. delete from big table via join to tmp table

4. Insert/select from tmp to big table


in case of low number

should be best for this problem.


Ulrich


SQL:

     CREATE PROCEDURE del_a (in a integer)
     BEGIN
     DECLARE U_ID INTEGER;
     SET U_ID = 0;
     FOR for_loop AS cursor1 CURSOR FOR
     SELECT cast(a as varchar(1000)) as a
     FROM a
     where a = :a
     DO
     SET U_ID = U_ID + 1;
     IF U_ID > 1 THEN
     delete from a WHERE CURRENT OF cursor1;
     END IF;
     END FOR;
     END;


     
  <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