Sql to delete duplicates in Teradata

Database
Enthusiast

Sql to delete duplicates in Teradata

Hi ,

Is there a single SQL statement to delete duplicate records from a table in Teradata.
Note , I don't want to use any intermediate table , like inserting into an intermediate set table.

Thanks in Advance.
17 REPLIES
Enthusiast

Re: Sql to delete duplicates in Teradata

delete from table a
where row_Number() over (partition by col1 col2 col3....coln) > 1
Enthusiast

Re: Sql to delete duplicates in Teradata

Hi emilwu,
I don't think we can ordered analytical functions in the where clause.
Regards,
Naveen K
Teradata Employee

Re: Sql to delete duplicates in Teradata

Hi ,

Please have a look on below teradata forum this will help you to understand various ways to delete duplicate rows from a table.

http://www.teradataforum.com/teradata/20040505_182346.htm

Thanks
Anand Agarwal
Consultant
ADC-India
Teradata Employee

Re: Sql to delete duplicates in Teradata

The question look simple but the response is not always easy!!
If there is dups, it indicates that we don't have UPI's or USI's and/or the table is MULTISET.
Let say that the simplest way is to use window ordered functions and qualifying one row :

Select ......
From Qualify row_number() over( partition by order by ) = 1

But we have to choose the right partitionning and ordering column-groups (PK's?, dates ? timestamps? sequence
numbers ? ). These questions are model-related. We must answer to theses questions first, and most of the time responses are not only technical.
Enthusiast

Re: Sql to delete duplicates in Teradata

hi Naveen,

select
id,count(*)
from group by id
having count(*)>1

gives you duplicates

Re: Sql to delete duplicates in Teradata

Try this...

delete from table where table.rowid not in (select max(table.rowid) from table group by col1,col2,col3.....)
Enthusiast

Re: Sql to delete duplicates in Teradata

I tried by creating the same table with UPI on the duplicate columns & loading it with multiload by suppressing the errors.
Enthusiast

Re: Sql to delete duplicates in Teradata

which function is used to find the duplicate rows in teradata?....................
and
which function is used to find the unique rows in teradata?..........
Enthusiast

Re: Sql to delete duplicates in Teradata

why we use fast load & multi load rather than BTEQ ........
tel some cases we go for only fast load and we go for only multi load........
and what are the best situations we go for the bteq