Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Delete all but some rows - Oracle

Tags:

sql

oracle

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.

like image 491
Guru Avatar asked Nov 27 '09 13:11

Guru


People also ask

What is truncate Oracle?

When you truncate a table, Oracle Database automatically removes all data in the table's indexes and any materialized view direct-path INSERT information held in association with the table. This information is independent of any materialized view log.

How can we improve the performance of delete statement in Oracle?

Parallelize deletes - Oracle parallel DML includes delete statements and you can parallelize large deletes for faster performance. You can also submit multiple, simultaneous delete statements against the same table, just make sure that you have enough freelists (or ASSM) to handle the concurrency.


3 Answers

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
  )
/
like image 114
David Aldridge Avatar answered Oct 04 '22 21:10

David Aldridge


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.

like image 35
Vincent Malgrat Avatar answered Oct 04 '22 20:10

Vincent Malgrat


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
);
like image 23
Tony Andrews Avatar answered Oct 04 '22 22:10

Tony Andrews