How to do a while loop to delete rows based on rowcount?

Question

I have the following scenario:

A table containing more than a million records. I need to create a loop that deletes 10'000 rows per execution (loop) based on the column number of the rows. So if in the end of the execution the column has 500 rows for example, then i need to delete all these rows.

So I imagined that this might work:

declare COUNTER int;

while (select count(*) from Table) >= 10000)
loop
     DELETE FROM (SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000);
endloop;

It's something like this that I need, but I don't know how to increase the Counter variable in the loop. And I suppose that I need to put an IF THEN ... ELSE... statement inside the WHILE to check the SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000, and in the ELSE, delete the rest of the rows that I have: i.e the last 500 rows.

EDIT:

I'm trying to make this work:

DECLARE COUNTER INTEGER :=0;
 BEGIN
 DBMS_OUTPUT.PUT_LINE('START'); 
loop -- keep looping 
COUNTER := COUNTER + 1;

  --do the delete 1000in each iteration
  Delete TEST where  rownum < 1000;

  -- exit the loop when there where no more 1000 reccods to delete. 
  exit when SQL%rowcount <= 1000;
  commit; 
end loop;
  DBMS_OUTPUT.PUT_LINE(COUNTER); 
 commit; 
END;

I put 10'000 rows in my TEST table. In the end the counter should be 9 and at the end of the loop the table should contain 1000 rows, but in my case the counter is 11 and the table is empty.

Why doesn't it work?

Answer 1

Try this:

DECLARE 
COUNTER INTEGER :=0;
CANT INTEGER;
 BEGIN
 DBMS_OUTPUT.PUT_LINE('START'); 
loop -- keep looping 
COUNTER := COUNTER + 1;

  --do the delete 1000in each iteration
  Delete TEST where  rownum <= 1000;

  -- exit the loop when there where no more 1000 reccods to delete. 
  -- DBMS_OUTPUT.PUT_LINE('SQL%rowcount: '|| SQL%rowcount); --this return 999 and breaks the while
  -- exit when SQL%rowcount <= 1000;
  SELECT COUNT(*) INTO CANT FROM TEST;
  exit when CANT <= 1000;
  commit; 
end loop;
  DBMS_OUTPUT.PUT_LINE('Counter: '||COUNTER); 
  DBMS_OUTPUT.PUT_LINE('Left: '||cant);
 commit; 
END;

I only change the stop condition of the while and rownum <= 1000. With 10000 rows it returns:

START
Counter: 9
Left: 1000

Left refers to remaining rows. I hope this helps.

Answer 2

As I understand the problem, you want to leave 1000 rows in a table that starts with > 1 million. In SQL Server, I ran I to problems with the delete statement locking the table, and also taking a long time to execute. All the deleted records have to go into the transaction log.

What worked better was to select the records I wanted to keep into a new table, and then rename the tables.

With Oracle, your mileage may vary