Sql to delete duplicates in Teradata

Database
Enthusiast

Re: Sql to delete duplicates in Teradata

how we find the new data in the table
Not applicable

Re: Sql to delete duplicates in Teradata

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;

Re: Sql to delete duplicates in Teradata

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>);

Supporter

Re: Sql to delete duplicates in Teradata

No it won't.

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

Cheers.

Carlos.

Not applicable

Re: Sql to delete duplicates in Teradata

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

Not applicable

Re: Sql to delete duplicates in Teradata

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)

Highlighted
Enthusiast

Re: Sql to delete duplicates in Teradata

Hi

Why cant we create a SET table and insert the values within it.

Which would automatically discard all the duplicate values

Not applicable

Re: Sql to delete duplicates in Teradata

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 !

Enthusiast

Re: Sql to delete duplicates in Teradata

SyntaxEditor Code Snippet

create table uniq_tab  as 
(select * from (select t.*,ROW_NUMBER() over (partition by Col1,col2,... order by 1) rn from  duplicate_table  t)X where rn=1) with data;
ALTER TABLE uniq_tab DROP rn;
delete  duplicate_table all;
insert into duplicate_table select * from uniq_tab ;
Senior Apprentice

Re: Sql to delete duplicates in Teradata

HI,

 

Going back to the original requirement:

 

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.

 

This is a simple DELETE command using a ROW_NUMBER function.

 

Assume that the table has columns c1,c2,c3,c4,c5 (extend with as many columns as necessary).

DELETE FROM t1
QUALIFY ROW_NUMBER() OVER(PARTITION BY c1, c2, c3, c4,c5 ORDER BY c1) > 1;

Note that all columns in the table definition must be included in the PARTITION BY clause. The ROW_NUMBER function requires an ORDER BY bu in this case which column(s) are included are irrelevant, so I've only included one.

 

HTH

Dave

 

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