Delete all but some rows - Oracle

Question

Hi,

I want to delete set of the rows from a table. I can decide which rows need to be deleted. I will delete the rows from table only if the count of rows is more than 5 (based on condition).

Consider this sample data

ID--Col1--Col2--
1   A      X
2   A      X
3   A      X
4   A      X
5   A      X
6   A      X
7   A      X
8   A      X
9   A      X
10   A      X
11   B      X
12   B      X
13   B      X
14   B      X
15   C      X
16   C      X
17   C      X
18   D      X
19   D      X

I want to delete 5 Rows of {*, A, X}, I need to keep 5 of them (no matter which one). I wont delete B, C & D since count of them is less then 5.

Like

delete from tableA
 --- I can decide on the rows to delete based on two conditions.
 where col1 = someCondition
   and col2 = someOtherCondition
   and rownum > 5 --- This dint work. I checked.

I think perhaps I need to programmaticaly. Any suggestions are greatly appreciated.

Answer 1

Hi Guru,

this will delete all rows except 20 rows that satisfy both conditions on Col1 and Col2:

DELETE FROM tableA
 WHERE ROWID NOT IN (SELECT ROWID
                       FROM tableA
                      WHERE col1 = someCondition
                        AND col2 = someOtherCondition
                        AND rownum <= 20)

If your data set is really large, the following may be faster:

CREATE tableTemp as 
SELECT *
  FROM tableA
 WHERE col1 = someCondition
   AND col2 = someOtherCondition
   AND rownum <= 20;

TRUNCATE tableA;

INSERT INTO tableA (SELECT * FROM tableTemp);

Replace the truncate by a DELETE if you need to access the data during the operation.

Answer 2

This will keep a maximum of 5 from each group:

delete mytable where rowid in
( select rowid from
  ( select rowid, row_number() over (partition by col1, col2 order by id) rn
    from mytable
  )
  where rn > 5
);

Answer 3

This will delete all rows for each unique combination of col1 and col2 other than the first five ordered by rowid

delete from my_table
where rowid in
  (
  select rowid
  from
    (
    select rowid,
           row_number() over (partition by col1, col2 order by rowid) rownumber
    from   my_table
    )
  where rownumber > 5
  )
/

Answer 4

ROWNUM>5 doesn't work because for the first affected row, ROWNUM is always 1; this condition also includes the "ROWNUM>5" part, so Oracle can't possibly find a row that fits the bill.

This might work:

delete from tableA
 --- I can decide on the rows to delete based on two conditions.
 where col1 = someCondition
   and col2 = someOtherCondition
   and rownum <= (select count(*) from tableA 
                    where col1 = someCondition
                      and col2 = someOtherCondition) - 5;