CODEWITHSUNDEEP
oracle10g
user2249376
user2249376

Reputation: 5

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

In Oracle 10g, I have a table with no exact duplicates, but many similar rows. This is OK, but I want to delete rows with one specific criteria in the collection of similar rows. The criteria is that multiple accounts are associated with one PRACTICE_NAME. I would like to delete all records with a NULL ACCT value when there is multiple accounts for that PRACTICE_NAME. However, if there is only one instance of a PRACTICE_NAME, and ACCT is NULL, I want to preserve the 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 is the result I would 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

Upvotes: 0

Views: 56

Answers (1)

donny
donny

Reputation: 333

You need to use the group on the practice_name to count how many record actually exists for each group. Using that information, you can find the list of PRACTICE_NAME with record > 1.

The SQL below will handle the case you mentioned. 

DELETE FROM ZZ_TEST ZZ
  WHERE ZZ.PRACTICE_NAME IN (
      SELECT PRACTICE_NAME FROM (  
           SELECT PRACTICE_NAME, COUNT (PRACTICE_NAME) AS COUNTER FROM ZZ_TEST
               GROUP BY PRACTICE_NAME) TMP_COUNTER
      WHERE COUNTER > 1)
      AND ACCT IS NULL;

Upvotes: 1

Related Questions