How do you eliminate duplicates?
Answers were Sorted based on User's Feedback
Answer / yuvaevergreen
yes...the above option can be used if access has been
provided to create set tables.If not, partition by clause
can be used.
1. create table2 as table1 with no data;
2. insert into table2
sel column1,column2,,,columnn from table1
group by 1,2
qualify row_number() over (partition by
column1,column2,,,columnn order by
column1,column2,,,columnn desc) =1
where table1 is with duplicates and table2 would be target
table.
Is This Answer Correct ? | 12 Yes | 0 No |
Answer / guest
Let us consider table ‘B’ containing duplicates.
Create a empty table ‘A’ with set option and send the data
from table ‘B’ to table ‘A ‘so that only unique records will
be inserted. Now drop table ’B’ and rename table ‘A’ with
table ‘B’.
Is This Answer Correct ? | 8 Yes | 0 No |
Answer / tdguy
Adding to the above,if enough spool space is available, 1.
create a volatile table with the same structure 2. insert
into volatile table
sel column1,column2,,,columnn from table1 group by 1,2
qualify row_number() over (partition by
column1,column2,,,columnn order by
column1,column2,,,columnn desc) =1
3. delete from target table and insert from volatile table.
All the above steps should be done with the same session.
Is This Answer Correct ? | 2 Yes | 0 No |
How many macros we can create inside a macro
Can you connect multiload from ab initio?
How can bottlenecks be identified?
how to delete duplicate records in multi set table without using any tables
2 Answers Accenture, Nest, South Nests Software Solution, Spiro Solutions,
Why Multiload and Fastload does not supports SI,JI,RI and TRIGGERS?
What are the different softwares used with their functions in teradata?
What are the string manipulation operators and functions associated with teradata?
Difference between multiload and tpump?
What is the purpose of using case expression in teradata?
What are the frequently used data types in teradata?
How to Skip or Get first and Last Record from Flat File through MultiLoad and TPUMP Utility?
what happen if a query fail in dispatcher?