Write a single SQL to delete duplicate records from the a
single table based on a column value. I need only Unique
records at the end of the Query.
Answer Posted / bharath
guys rowid concept is discontinued in teradata as far as my
knowledge goes, we can always use below sql ...
INSERT INTO nodupes_table ( all_columns )
SELECT all_columns
FROM dupes_table
QUALIFY ...
where the QUALIFY ... can be (depending on your version):
/* V2R5 syntax */
QUALIFY ROW_NUMBER() OVER (PARTITION BY all_columns
ORDER BY all_columns) = 1
/* or V2R4 and higher equivalent functionality */
GROUP BY all_columns
QUALIFY CSUM(1, all_columns ) = 1
/* or, alternative OLAP SUM V2R4 and higher syntax */
QUALIFY SUM(1) OVER (PARTITION BY all_columns ORDER BY
all_columns ROWS
UNBOUNDED PRECEDING ) = 1
I do think an insert-select into a set table would be a
cleaner process (don't know about runtime, though):
INSERT INTO nodupes_set_table ( all_columns )
SELECT all_columns
FROM dupes_table;
refer to teradata forum for more info
Is This Answer Correct ? | 17 Yes | 5 No |
Post New Answer View All Answers
Differentiate database data and data warehouse data?
Explain Teradata performance tuning and optimization?
Highlight all the modes that are present under Confidence Level.
How to explain project Architecture and flow in teradata interviews?Can please anyone help on this? Am new to teradata.
How to Extract data from multiple legacy systems?
Explain teradata architecture?
What is difference between user and database in teradata?
What is a node in teradata? Explain
What are the different design perspectives used in teradata?
Why teradata is used?
What is the particular designated level at which a LOCK is liable to be applied in Teradata?
In a table can we use primary key in one column and in another column both unique and not null constrains.if yes how?
What are the uses of client software involved in teradata?
i learn teradata,it is so intersting,now i want teradata certifications ,so any 1 help me to get teradata certifications TD 12 ? email:kdcrazyy@gmail.com
If RDBMS is halted what will you do ?