PL/SQL cannot delete multiple rows

Question

I have written simple code using PL/SQL to delete multiple rows from a table, but below code only deletes one row every i trigger it.

DECLARE
    i number(2);
BEGIN
    FOR i IN 1..4 LOOP
        DELETE FROM table_name WHERE rownum = i;
        dbms_output.put_line('i is: '|| i);
    END LOOP;
END;

Can someone please suggest what is wrong with code?

Have a look at https://stackoverflow.com/questions/32959404/mystery-of-rownum-in-oracle, https://stackoverflow.com/questions/30321483/how-rownum-works-in-pagination-query, [www.orafaq.com/wiki/ROWNUM](http://www.orafaq.com/wiki/ROWNUM) — William Robertson, May 28 '18 at 07:56
Also consider using FORALL statement, it's quite inefficient to delete rows in for loop — Ilia Maskov, May 28 '18 at 08:14

score 5 · Answer 1 · answered May 28 '18 at 07:47

5

ROWNUM is the nth row read.

select * from table_name where rownum = 1;

gets you the first row.

select * from table_name where rownum <= 2;

gets you the first two rows.

select * from table_name where rownum = 2;

gets you no rows, because you cannot read a second row without having read a first one.

This said, you'd have to replace

DELETE FROM table_name WHERE rownum = i;

with

DELETE FROM table_name WHERE rownum = 1;

But why would you do this anyway? Why delete arbitrarily picked records? Why use PL/SQL at all, rather than a mere DELETE FROM table_name WHERE rownum <= 4;?

answered May 28 '18 at 07:47

Thorsten Kettner

89,309
7
49
73

Thanx for the reply.. this few lines are the part of PLSQL procedure ,purpose of the code is delete multiple rows using PLSQL – Avishekkj May 28 '18 at 08:55
Yes, but usually one would simply use SQL (`delete from table_name where ...`) to delete multiple rows. Why do you want to use PL/SQL (a programming language) for this? And why are you deleting random rows? Don't you want to delete specific rows? – Thorsten Kettner May 28 '18 at 09:03

score 1 · Answer 2 · answered May 29 '18 at 07:42

What you need to understand is how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2 or equal to nth value, no rows are selected and ROWNUM is never incremented beyond 1.

If you really do wanna achieve it using PLSQL anot using SQL query as my friend Throsten has stated then please find a work around below.

I Created a dummy table test_c which holds 1 column (ID with number as its type).

set serveroutput on ;
DECLARE
 i number(2);
 j number(2);
 counter number(10):=0;
BEGIN
  FOR i IN 5..11 LOOP
   if counter = 0 then
      j:=i;
   end if;
   DELETE FROM test_c WHERE ID = (select id from (select id,rownum as ro from test_c order by id) where ro =j); 
   dbms_output.put_line('i is: '|| i);
   counter:=counter+1;
  END LOOP;
END;

Please note that this is not the right way to do it, but will work for your requirement.

PL/SQL cannot delete multiple rows

2 Answers2