-
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 :?
-
I've done this via a PL/SQL-table. You wanna do it with a single SQL?
-
yea with SQL if possible, if not then pl-sql
-
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;
/
-
burps looks nasty
I will try a bit more with sql if not possible then have to use plsqL
thx
-
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);
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|