delete rows by rownum
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: delete rows by rownum

Hybrid View

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218

    delete rows by rownum

    Hi. I have a tbale that contains 800K rows.
    I want to delete the first 100K rows. I have written this script, but
    it does not seem to work. Can someone assist or have another method ?
    Thanks.

    declare
    cursor a1 is select 1 from dual;
    x number:=1;
    begin
    for i in 1..100000 loop
    delete t_bitmap1_hold where rownum=x;
    end loop;
    end;

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    define what the first 100k rows are? any 100k? or defined by a column

  3. #3
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218
    Not sure what you are asking ?
    Its a table made of many columns. There is no column in my table that references a row id. The table contains 800K rows. I want to remove the first 100K rows. I thought each row in a table has a rownum ?
    I just wanted to loop until I delete the first 100K.
    My script might be invalid ? Is there a better way ? or can I alter my script ?
    Thanks for your help ?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    no every row in a table doesnt have a rownum, so which 100k rows do you want to delete? what do you mean by 'first' - is it a random 100k or specific based on some field

  5. #5
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218
    Its a random delete. I want to do this in my plsql script.
    If I can't use rownum, how can I loop and delete the first 100K or any 100K?
    Thanks.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by pascal01
    Its a random delete. I want to do this in my plsql script.
    If I can't use rownum, how can I loop and delete the first 100K or any 100K?
    Randomly delete 100K rows?
    I would love to see the rationale behind such an unusual business requirement
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218
    This is test data. I am doing performance testing. Writing a script to delete rows in a loop and not a bulk delete. So that is the rationale behind this unusual business requirement. Monsieur !

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    Quote Originally Posted by pascal01
    This is test data. I am doing performance testing. Writing a script to delete rows in a loop and not a bulk delete. So that is the rationale behind this unusual business requirement. Monsieur !

    so writing code to do things in the slowest way possible

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    delete from table where rownum < 100001

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Merci beaucoup but, still unusual
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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