All Forums Database
kartik.sitapara 7 posts Joined 12/10
04 Jun 2012
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

ulrich 816 posts Joined 09/09
04 Jun 2012

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/

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

S_Banerjee 15 posts Joined 02/09
04 Jun 2012

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

 

 

 

kartik.sitapara 7 posts Joined 12/10
06 Jun 2012

Thanks for the wonderfull help. 

You must sign in to leave a comment.