Hello,
I am trying to remove duplicate values from below table based on condition.
trying to delete records based on column n_p1 and n_p2 and (id, name, address, city, state)
If count (id, name, address, city, state) >1 and n_p1 is not null or n_p2 is not null then remaining records should be deleted where n_p1 and n_p2 are null.
If count (id, name, address, city, state) = 1 then no need to delete the record.
select 101 id, 'Project1' name, 'X' address, 'CS' city, 'CA' state, 12 n_p1, null n_p2 from dual
union all
select 101 id, 'Project1' name, 'X' address, 'CS' city, 'CA' state, 12 n_p1, 10 n_p2 from dual
union all
select 101 id, 'Project1' name, 'X' address, 'CS' city, 'CA' state, null n_p1, null n_p2 from dualĀ -- should be removed
union all
select 102 id, 'Project2' name, 'Y' address, 'CT' city, 'DM' state, null n_p1, null n_p2 from dual
union all
select 103 id, 'Project3' name, 'Z' address, 'CW' city, 'CM' state, 5 n_p1, null n_p2 from dual
union all
select 103 id, 'Project3' name, 'Z' address, 'CW' city, 'CM' state, null n_p1, 6 n_p2 from dual;
Please help me out to solve this.
Thanks