Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Delete duplicate records based on condition

3639779Oct 30 2018 — edited Nov 2 2018

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

This post has been answered by mathguy on Oct 30 2018
Jump to Answer
Comments
Post Details
Added on Oct 30 2018
1 comment
1,010 views