Delete duplicate rows(keep only one row from duplicate rows)

Database
Enthusiast

Delete duplicate rows(keep only one row from duplicate rows)

I am trying the below query.
But it is not working

select col1,col2,col3,row_number() over(partition by col1,col2,col3 order by col1,col2,col3 desc) as table_rank from x.testing_ms;

*** Query completed. 3 rows found. 4 columns returned.
*** Total elapsed time was 1 second.

COL1 COL2 COL3 table_rank
----------- ----------- ----------- -----------
1 2 3 1
1 2 3 2
2 3 4 1

delete from x.testing_ms a,
(select col1,col2,col3,row_number() over(partition by col1,col2,col3 order by col1,col2,col3 desc) as table_rank from x.testing_ms) b
where a.col1=b.col1
qualify b.table_rank > 1;

5 REPLIES
Enthusiast

Re: Delete duplicate rows(keep only one row from duplicate rows)

*** Failure 3706 Syntax error: derived table not allowed for delete.
Statement# 1, Info =222
*** Total elapsed time was 1 second.
Enthusiast

Re: Delete duplicate rows(keep only one row from duplicate rows)

perhaps give a WITH derived table a go.

Otherwise I'd create a Volatile table and split the aggregation straight out of the DELETE...

WITH dups(col1, col2, col3, table_rank) AS
(
select
col1
,col2
,col3
,row_number() over
(
partition by col1,col2,col3
order by col1,col2,col3 desc
) as table_rank
from x.testing_ms
qualify table_rank > 1;
)
delete from x.testing_ms a
where a.col1 in (sel col1 from dups);
Enthusiast

Re: Delete duplicate rows(keep only one row from duplicate rows)

FYI

3706 Syntax error: %VSTR.

Explanation: The request contains a syntax error.
’%VSTR’ identifies the approximate location of the error
or identifies the syntax problem.
Generated By: SYN modules.

For Whom: End User.

Notes: The failure parcel INFO field contains the character
position of the error location in the request parcel.
In addition to syntax errors, this error message may also
indicate a problem such as: duplicate index or constraint
names, misspelled function names or function
parameters, or cast to an invalid or non-existent data
type.

Remedy: Correct the syntax and resubmit the request
Enthusiast

Re: Delete duplicate rows(keep only one row from duplicate rows)

With any luck you won't get a:
5606 Statistical Functions not allowed in WITH
clause.
Explanation: Statistical Functions not allowed in
WITH clause.
Generated By: Opt modules
For Whom: End User
Junior Supporter

Re: Delete duplicate rows(keep only one row from duplicate rows)

Discussed many times...

Many ways to do it.

One of them here (in spanish, as always):

http://carlosal.wordpress.com/2009/12/07/borrando-filas-duplicadas-en-teradata/

HTH.

Cheers.

Carlos.