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;
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>);
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
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
try the below query. Hope it helps
DELETE FROM TEST4 TT1
Why cant we create a SET table and insert the values within it.
Which would automatically discard all the duplicate values
"How to remove duplicate more than one records from given table. table as tbl1
i want output like
Thanks in Advance !
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 ;
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.