Re: How to delete all rows in a table except for 10 of them?

From: L120bj <l120bj_at_aol.com>
Date: 1997/03/20
Message-ID: <19970320185401.NAA12481@ladder01.news.aol.com>#1/1

Subject: How to delete all rows in a table except for 10 of them? From: wdieteri_at_rmi.net (Will Dieterich)
Date: 19 Mar 1997 17:35:00 GMT
Message-ID: <5gp844$1ib$1_at_news1.rmi.net>

I have over 200 tables(with various numbers of rows) where I need to delete
all but 10 rows from each of them(rows that remain are not important, just that each of the tables will only have 10 rows when it is completed).

Needless to say the psedudo-row rownum will not work with this, so I am looking some quick and dirty way of doing it. You could do

delete from tableA
where rowid not in ( select rowid from tableA where rownum < 11);

this would only need the table name to be altered and so could be generated from a piece of sql. However, depending on how many rows you have in each table the jobs could take sometime, and the indexes on the tables could become quite fragmented.

Another method would be to create a tempory account and do the following :-
create table dummy_account.tableA as
select * from tableA
where rownum < 11;

truncate tableA;

insert into tableA select * from dummy_account.tableA;

drop table dummy_account.tableA;

This wpuld probably run much quicker and would also be far more efficient in both the amount of space used and structure of the indexes.

Hope this helps
Rob

---

---

So far the best way I can determine do to this is to sub-select inside the delete statement that will select the first 10 rows, here rownum will work,
and then delete the rows
that are not in sub-select. Unfortunatly, less then half of the tables have
any similar column names, so I am looking at a fair amount of manual work.

-- 
William Dieterich      Call Sign: KD4LZE       Email: wdieteri_at_rmi.net
"As the flowers are all made sweeter by the sunshine and the dew, so this
 old world is made better by the lives of folks like you" inscription on
the
tombstone of Bonnie Parker of Bonnie and Clyde fame.

Received on Thu Mar 20 1997 - 00:00:00 CST