CODEWITHSUNDEEP
sqloracle-database
M. Rogers
M. Rogers

Reputation: 1

Oracle SQL - Delete Entries Based Off Unique Rows

I am pulling a single column from a DB and it looks something like this:

Group
A
A
A
B
B
B
C
D
D
D
E
F
F
F

I need to delete unique entries, so entries A, B, D and F should stay and entries C and E should be deleted.

I am getting this row based of a query like this:

select Group from table where type = 'rec';

and basically each type should have more than one group and if it doesn't it needs to be removed.

NOTE: I need it to be automated and not just a "remove C" and "remove E" because there are thousands of rows and I'm not sure which I will need to delete unless I just find them. The number of rows that will need to be deleted will also be changing, hence why I need it to be automated based off of count.

Upvotes: 0

Views: 67

Answers (2)

Art
Art

Reputation: 5782

Judging by your comments all you need is running total. If entry occurred once then select/delete it. The analytic functions is the best and easiest way if you ask me:

SELECT * FROM 
(
 SELECT COUNT(grp) OVER (PARTITION BY grp ORDER BY grp) cnt -- number of occurances --
       , grp 
   FROM
   ( -- convert to multi-row - REPLACE AAABBB with your actual column --
    SELECT trim(regexp_substr('A A A B B B C D D D E F F F', '[^ ]+', 1, LEVEL)) grp
      FROM dual -- from your table_name --
    CONNECT BY LEVEL <= regexp_count('A A A B B B C D D D E F F F', '[^ ]+')
   )
  )
 WHERE cnt = 1 -- Select/Delete only those that appeared once -- 
 /

Output:

cnt|grp
--------
1   C
1   E

Full output, if you comment where:

cnt|grp
--------
3   A
3   A
3   A
3   B
3   B
3   B
1   C
3   D
3   D
3   D
1   E
3   F
3   F
3   F

Final edit based on your questions. This simulates your table:

WITH your_table AS
(
 SELECT 'rec' grp_type FROM dual
 UNION ALL
 SELECT 'not_rec' grp_type FROM dual
 )
 SELECT grp_type FROM your_table WHERE grp_type = 'rec' -- apply all that above to this select --
 /

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

One method is:

delete t
    where "group" in (select "group" from t group by "group" having count(*) = 1);

Based on your sample code:

delete t
    where type = 'rec' and
          "group" in (select "group" from t where type = 'rec' group by "group" having count(*) = 1);

You could also do this as:

delete t
    where type = 'rec' and
          not exists (select 1
                      from t t2
                      where t2.group = t.group and t2.type = 'rec' and t2.rowid <> t.rowid
                     );

Upvotes: 2

Related Questions