Duplicate Delete

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

Duplicate Delete

Hi All,

 

I am receiving feeds from source as below:

 

Table:-

Primary_ID| Child_ID|parent_id

1| abc|xyz

2|xyz|abc

3|pqr|uvw

4|uvw|pqr

 

So we have bidrectional relationship where parent is child and vice versa and all the relations are similarly duplicated..

 

Can someone suggest a query to delete one and keep other?

 

It doesnt matter which one we keep and which we delete.

 

I figured its not a normal query and would need a cursor.

 

Any suggestions?

 

2 REPLIES 2
Teradata Employee

Re: Duplicate Delete

Hi zishan,

 

You can use least and greatest functions to have both columns in a similar order accross rows.

Data

create multiset volatile table mvt_datas, no log
( primary_Id    byteint not null
, Child_ID      varchar(30)
, parent_id     varchar(30)
)
unique primary index (primary_Id)
on commit preserve rows;

insert into mvt_datas values (1, 'abc', 'xyz');
insert into mvt_datas values (2, 'xyz', 'abc');
insert into mvt_datas values (3, 'pqr', 'uvw');
insert into mvt_datas values (4, 'uvw', 'pqr');

collect statistics column (primary_Id) on mvt_datas;

Query

  select primary_Id, Child_ID, parent_id
    from mvt_datas
 qualify row_number() over(partition by least(Child_ID, parent_id)
                                      , greatest(Child_ID, parent_id)
                               order by primary_Id asc) = 1
order by primary_Id asc;

primary_Id  Child_ID  parent_id
----------  --------  ---------
         1  abc       xyz
         3  pqr       uvw

 

 

 

Fan

Re: Duplicate Delete

Thanks Waldar, let me try this out .