oracle10g - How can I delete similar rows with very specific criteria among similar rows? -

in oracle 10g, have table no exact duplicates, many similar rows. ok, want delete rows 1 specific criteria in collection of similar rows. criteria multiple accounts associated 1 practice_name. delete records null acct value when there multiple accounts practice_name. however, if there 1 instance of practice_name, , acct null, want preserve record.

sample data: acct    practice_name   state   phone ======================================= null      pract1         mi    111-1111 1523      pract1         mi    111-1111 6824      pract1         mi    111-1111 null      pract2         mi    222-2222 8945      pract2         mi    222-2222 null      pract3         mi    333-3333 1486      pract4         mi    444-4444 

this result like:

acct    practice_name   state   phone ======================================= 1523      pract1         mi    111-1111 6824      pract1         mi    111-1111 8945      pract2         mi    222-2222 null      pract3         mi    333-3333 1486      pract4         mi    444-4444 

you need use group on practice_name count how many record exists each group. using information, can find list of practice_name record > 1.

the sql below handle case mentioned.

delete zz_test zz   zz.practice_name in (       select practice_name (              select practice_name, count (practice_name) counter zz_test                group practice_name) tmp_counter       counter > 1)       , acct null;