-
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;
-
define what the first 100k rows are? any 100k? or defined by a column
-
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 ?
-
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
-
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.
-
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.
-
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 !
-
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
-
delete from table where rownum < 100001
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|