delete bottom n-rows

[pando]

Hi

I wonder if there is a way to delete rows between let´s say rownum 15 and rownum 30, it´s easy selecting but with delete I get ORA-01732

anyone know any SQL that might do this :?

[julian]

I've done this via a PL/SQL-table. You wanna do it with a single SQL?

[pando]

yea with SQL if possible, if not then pl-sql

[julian]

Make the PL/SQL-table to store the rowids of the rows to be deleted and .... well here is script I would suggest you, I call your table THE_TABLE and we delete from row 15 till row 30.

set serveroutput on
declare
TYPE PANDO_TYPE IS TABLE OF rowid
INDEX BY BINARY_INTEGER;
W_TABLE PANDO_TYPE;
cursor C_CURSOR_1 is
select
ROWID
from
THE_TABLE
where
ROWNUM <= 30;
i pls_integer; N pls_integer;
X rowid;
begin
dbms_output.enable(50000);
i := 0; N := 0;
open C_CURSOR_1;
loop
fetch C_CURSOR_1 into X;
if C_CURSOR_1%notfound then exit; end if;
i := i+1; W_TABLE(i) := X;
end loop;
close C_CURSOR_1;
for i in 15..W_TABLE.COUNT loop
delete from THE_TABLE where rowid=W_TABLE(i);
N := N+1;
end loop;
commit work;
dbms_output.put_line(CHR(10)||'DELETED ROWS: '||N);
end;
/

[pando]

burps looks nasty

I will try a bit more with sql if not possible then have to use plsqL

thx

[jmodic]

This deletes rows where ROWNUM is between 5 and 10:

delete from scott.emp where rowid in
(select rowid from scott.emp where rownum <= 10
minus
select rowid from scott.emp where rownum <= 5);

[pando]

uh cheers

thx

^^;

^_^