single Sql to delete duplicates in Teradata

General
Enthusiast

single Sql to delete duplicates in Teradata

Hi Team,

I want a single query to delete duplicates in Teradata.

I have tried with below but its not working

delete from table qualify row_number() over(partition by id order by id) >1

1 abc

1 abc

1 abc

2 xyz

2 xyz

3 def

3 def

o/p

1 abc

2 xyz

3 def

don't use any intermidiade table

 

Thanks

Mt

 

3 REPLIES 3
Teradata Employee

Re: single Sql to delete duplicates in Teradata

You can't do this in a single SQL DML statement

Enthusiast

Re: single Sql to delete duplicates in Teradata

Thanks Fred. We are using volatile set table and again reinserting into main table after truncate.

I thought if is there any other way to write this in sngle query.

 

Ambassador

Re: single Sql to delete duplicates in Teradata

Simply don't insert those duplicate rows :-)

E.g. using MERGE