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)
4 REPLIES
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!