Now i got a scenario today where i need to update the duplicate row created in the table. I knew that would be easy but there was a trick in this scenario. The duplicate is considered only when you look into two rows and some column combination.

description:
Parent_id - > parent identifier id
child_id - > child identifier id
virtual_parent - > virtual parent identifier id
virtual_relation - > 0 - real , 1 - user defined , 2 - virtual parent involved.

Below are the demo scripts:

create table tin.demo_child(parent_id number(10),child_id number(10),virtual_parent number(10),virtual_relation number(3));
create public synonym demo_child for tin.demo_child;

insert into demo_child select 11,22,NULL,0 from dual;
insert into demo_child select 12,22,11,2 from dual;
insert into demo_child select 17,22,NULL,0 from dual;
insert into demo_child select 15,22,NULL,1 from dual;
insert into demo_child select 11,25,NULL,1 from dual;
insert into demo_child select 17,25,11,2 from dual;
insert into demo_child select 17,29,NULL,0 from dual;
insert into demo_child select 15,29,NULL,1 from dual;

Now Run the below statement:
select * from demo_child;

virtual parent is virtual id assigned by the user and in this case its ID would differ from the actual parent_id.

below is a duplicate record:
parent_id = 11,child_id = 22,virtual_parent = null & parent_id = 12,child_id = 22,virtual_parent=11

I tried few things to update it but never got close.

create table demo_child(parent_id number(10),child_id number(10),virtual_parent number(10),virtual_relation number(3));

insert into demo_child select 11,22,NULL,0 from dual;
insert into demo_child select 12,22,11,2 from dual;
insert into demo_child select 17,22,NULL,0 from dual;
insert into demo_child select 15,22,NULL,1 from dual;
insert into demo_child select 11,25,NULL,1 from dual;
insert into demo_child select 17,25,11,2 from dual;
insert into demo_child select 17,29,NULL,0 from dual;
insert into demo_child select 15,29,NULL,1 from dual;

select coalesce(virtual_parent, parent_id), child_id from demo_child
group  by coalesce(virtual_parent, parent_id), child_id
having count(*) > 1;

COALESCE(VIRTUAL_PARENT,PARENT_ID)  CHILD_ID  
11                                  25        
11                                  22