I am receiving feeds from source as below:
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.
You can use least and greatest functions to have both columns in a similar order accross rows.
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;
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