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

Will Dieterich wrote:
>
> 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.
> 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.

PL/SQL:
for each table:

DECLARE
  tblRows NUMBER;
BEGIN
  SELECT count(*)
  INTO tblRows
  FROM myTable;

  DELETE FROM myTable
  WHERE rownum < tblRows-10;

  COMMIT;
END;
..

You could even write a script to generate the above code for each or your 200 tables if you don't want to have to change "myTable" to your actual table names 200 times. Also, if any of your tables have LOTS of rows, you may need a large rollback segment to handle huge deletes.
-- TRW Received on Wed Mar 19 1997 - 00:00:00 CST