delete bottom n-rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: delete bottom n-rows

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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 :?

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I've done this via a PL/SQL-table. You wanna do it with a single SQL?


  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    yea with SQL if possible, if not then pl-sql

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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;
    /

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    burps looks nasty

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

    thx

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    uh cheers

    thx

    ^^;

    ^_^



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width