All Forums Database
Naveen_K 22 posts Joined 08/09
07 Aug 2009
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.

emilwu 72 posts Joined 12/07
08 Aug 2009

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

Naveen_K 22 posts Joined 08/09
08 Aug 2009

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

anandagarwal 9 posts Joined 08/09
01 Sep 2009

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

Vador 36 posts Joined 08/07
04 Sep 2009

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.

sk73 52 posts Joined 07/09
15 Sep 2009

hi Naveen,

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

gives you duplicates

somprakash 1 post Joined 05/10
28 May 2010

Try this...

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

gotuchintu 32 posts Joined 12/05
02 Jun 2010

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

Somnath Roy

mahe 9 posts Joined 03/11
01 Mar 2011

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

mahe 9 posts Joined 03/11
01 Mar 2011

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

mahe 9 posts Joined 03/11
01 Mar 2011

how we find the new data in the table

BdeTheije 1 post Joined 05/09
05 Jun 2012

Not a single statement but it works :

Create table tmp as

( select * from table

qualify row_number() over (partition by pk_table order by pk_table) = 1

) with data;

Delete from table all;

Insert into table select * from tmp;

Drop table tmp;

 

NagamaiyaK 2 posts Joined 07/12
09 Jul 2012

try the below query. it will fulfill your requirement. 

delete from <Table_Name> where <Table_Name>.rowid
not in (select max(<Table_Name>.rowid) from <Table_Name>
group by <C1>, <C2>);
 

CarlosAL 512 posts Joined 04/08
09 Jul 2012

No it won't.

Please, do not post if you don't know what you are talking about.

Cheers.

Carlos.

Jacobz 1 post Joined 01/13
15 Jan 2013

Hi,
Did anyone get the exact solution for this?. When I have gone through all the statements above I couldnt see the correct one! Let me start from the top,
Please correct me if I'm wrong,
@emilwu I dont think we can use ordered analytical functions in the where clause. what naveen told is correct it seems. Instead of where clause we should use qualify()
@Vador I think you are correct in statement but
Select ......
From
Qualify row_number() over( partition by order by )=1
 
this gives all records except the duplicates, almost equivalent to using distinct clause which wont helps the requirement.
@sk73 The query is absolutely correct but it also wont meet the requirement. it just selects but on applying the delete it will delete all records having duplicates.
@BdeTheije The logic is absolutely correct  but your query straightway selects the distinct records using intermediate table but the requirement is we are not supposed to use it right?
So if anyone got the correct answer please update us here
Thanks! 
Joe
 
 
 
 
 
 
 
 
 
 

shikharaina 1 post Joined 09/15
22 Sep 2015

try the below query. Hope it helps
DELETE FROM TEST4 TT1

      WHERE ROWNUM < (  SELECT MAX (ROWNUM)

                          FROM TEST4 TT2

                         WHERE TT1.NAME = TT2.NAME

                      GROUP BY NAME)

gskaushik 56 posts Joined 09/10
26 Sep 2015

Hi
 
Why cant we create a SET table and insert the values within it.
Which would automatically discard all the duplicate values

Regards
Subramanian kaushik Gurumoorthy

04 Sep 2016

 
Hi....
"How to remove duplicate more than one records from given table.  table as tbl1

emp_id,emp_fname,emp_lname

1,a,b
2,a,b
2,a,c
3,c,b
3,f,h

i want output like
2,a,c
3,c,b
3,f,h
 
Thanks in Advance !
 
 

You must sign in to leave a comment.