Delete duplicate rows from Table in Teradata database

Database
Enthusiast

Delete duplicate rows from Table in Teradata database

Hi, 

I want to delete the dulplicate rows from the multiset table. I don't have access to create another table in database. Please help me to delete the duplicate rows from table without using another table. 

Thanks,

Kartik Sitapara

Tags (1)
8 REPLIES
Senior Supporter

Re: Delete duplicate rows from Table in Teradata database

Try google search for

Delete duplicate rows teradata

And Carlos posted some code here:

http://carlosal.wordpress.com/2009/12/07/borrando-filas-duplicadas-en-teradata/

Enthusiast

Re: Delete duplicate rows from Table in Teradata database

This above approach is good but seems a bit complex to me..use the power of Volatile table in this scenario...if you have full row duplicate issue then it will work superbly..see the approach below..

Step 1:

CREATE SET VOLATILE TABLE YOUR_VOLATILE_TABLE AS

(

SEL *

FROM YOUR_MAIN_TABLE

) WITH DATA ON COMMIT PRESERVE ROWS

;

Step 2:

DELETE FROM YOUR_MAIN_TABLE;

step 3:

INSERT INTO YOUR_MAIN_TABLE

SEL * FROM YOUR_VOLATILE_TABLE;

Thanks,

Banerjee

Enthusiast

Re: Delete duplicate rows from Table in Teradata database

Thanks for the wonderfull help. 

New Member

Re: Delete duplicate rows from Table in Teradata database

Thank you Banerjee for this elegant solution to the duplicate issue!

Teradata Employee

Re: Delete duplicate rows from Table in Teradata database

Test1 is an original table containing duplicates.

Test2 is a table without duplicate generated from test1.

 

 

have a look,

 

SyntaxEditor Code Snippet

create table test2
as  
(sel test1.*,row_number() over(partition by eid order by eid) as "Num"
From test1
qualify num=1)with data;
--------------------------------
sel * from test2;
alter table test2
        drop num;

.

 

Senior Apprentice

Re: Delete duplicate rows from Table in Teradata database

Hi,

 

@siddhantgives a good solution, but you can make it possibly better.

 

You don't need to include the 'num' column in the target table, which then means that you don't have to 'alter table' afterwards. Try:

create table test2
as  
(sel test1.*
From test1
qualify row_number() over(partition by eid order by eid)=1)with data;
--------------------------------
sel * from test2;

It also means that this approach can be used to build a volatile table (the original post said the user cannot create another table) - you cannot 'alter table' on a volatile table.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Delete duplicate rows from Table in Teradata database

Of course this is not checking for Duplicate Rows unless you add all columns in the PARTTION BY :-)

Teradata Employee

Re: Delete duplicate rows from Table in Teradata database

This way, we can delete duplicates without using new table,

I have created demo table(Test5),

SyntaxEditor Code Snippet

CREATE MULTISET TABLE TEST5 ( eid int,
     ename char(8)
    );

INSERT INTO TEST5 VALUES(1,'sid');--Run for 3-4 times
INSERT INTO TEST5 VALUES(2,'Raj');--Run for 3-4 times
ALTER TABLE TEST5               ---Adding New column for identifying purpose.    ADD RANKING INT;

INS INTO TEST5
    SEL eid, ename,ranking
    FROM                    (SEL eid, ename, 
                            ROW_NUMBER() OVER(PARTITION BY eid,ename 
                                                                    ORDER BY 1,2) as ranking
                    FROM test5) as A
    WHERE ranking =1;
--------------------------------------------DELETE FROM test5 WHERE ranking is NULL; -----deleting the duplicates 
-----------------------------------ALTER TABLE test5                ---Droping the non-usable coldrop ranking;