How to delete duplicate records in teradata table ?
For deleting duplicate records we have different ways.I have a Product_table.
Table name: product_table
product_id product_name
2 pqr
2 pqr
8 klm
8 klm
99 qqqq
Using rowid remove the duplicate records.This query will work only SQL
Syntax: delete from where rowid not in( select max(rowid) from group by );
Query: delete from product_table where rowid not in(select max(rowid) from
product_table group by product_id);
This is another way to delete duplicates. This will work in teradata.
here first create a set table with the definition then insert the records into set table. Al ready we know the set table does not allow duplicate records.
create set table product_table_t as (select * from product_table) with no data;
insert into product_table_t select * from product_table;
drop table product_table;
rename table product_table_t as product_table
using distinct, eliminating duplicate records
create temporary table with no data
Query : create table product_t as (select * from product_table) with no data
insert records in to table product_t with using distinct
insert into product_t select distinct * from product_table
drop the the product_table
rename table
rename table product_t as product
Please comment here if you know any other method of removing duplicates.
create set table product_table_t as (select * from product_table) with no data;
insert into product_table_t select * from product_table;
drop table product_table;
rename table product_table_t as product_table
using distinct, eliminating duplicate records
create temporary table with no data
Query : create table product_t as (select * from product_table) with no data
insert records in to table product_t with using distinct
insert into product_t select distinct * from product_table
drop the the product_table
rename table
rename table product_t as product
Please comment here if you know any other method of removing duplicates.