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

Hi William,

set pause off
set timing off
set time off
set feedback off
set heading off
set echoIn off
spool slave.sql

select 'delete from '||table_name||'

   where rowid not in (

      select rowid from '||table_name||' where rownum < 11);' from user_tables;

spool off
start slave.sql

Got it ?
Does this beast do what you want ?
Please understand and verify before use,

Kind regards,

Toni

article <5gp844$1ib$1_at_news1.rmi.net>, wdieteri_at_rmi.net (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.
>
> --
> 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.
 

-- 
A. Dischner, SGI&AIX sysadmin, Oracle DBA       | Don't let friends
Institut fuer Klinische Chemie                  | use WinDose
Klinikum Grosshadern                            | Just say NO. 
Ludwig Maximilians Universitaet Muenchen,  GER  |            
Marchioninistr.15 81366 Muenchen 49-89-70953202 |