oracle bulk delete : delete from table where column in array

Question

I am trying to collect an array of revisions numbers.
I will use to delete records in multiple audit tables.
So I wrote a plsql to collect that array and to run delete on other tables based on condition using that array

declare 
  type NumberArray is table of number index by binary_integer;
  revisions NumberArray;
Begin
  select rev bulk collect into revisions from (
    select t.rev, row_number() over (partition by 
        column1,
        column2
        order by column3) rn
    from table1 t)
  where rn <> 1;

  dbms_output.put_line(revisions.count || ' records found from table2 to be deleted');
  delete from table2 where rev in (revisions);
  dbms_output.put_line('deleted from table2');

I am getting

 PLS-00382: expression is of wrong type
 06550. 00000 -  "line %s, column %s:\n%s"
 *Cause:    Usually a PL/SQL compilation error.

Datatype of rev is number in table1.

If data is huge, then context switching will be huge, PL/SQL will be slower, much slower than SQL. — Lalit Kumar B
– Lalit Kumar B, Commented Jan 13, 2015 at 8:51

Marcin Wroblewski · Accepted Answer · 2015-01-13 07:52:15Z

This is a good candidate for FORALL statement

declare 
  type NumberArray is table of number(10) index by binary_integer;
  revisions NumberArray;
Begin
  select rev bulk collect into revisions from (
    select t.rev, row_number() over (partition by 
        column1,
        column2
        order by column3) rn
    from table1 t)
  where rn <> 1;

  dbms_output.put_line(revisions.count || ' records found from table2 to be deleted');
  forall i in revisions.first .. revisions.last
       delete from table2 where rev = revisions(i);
  dbms_output.put_line('deleted from table2');
End;

But if you still insist on using IN condition, you have to define your type at schema level:

create type NumberArray as table of number;

and then use it like this

declare 
  revisions NumberArray;
Begin
  select rev bulk collect into revisions from (
    select t.rev, row_number() over (partition by 
        column1,
        column2
        order by column3) rn
    from table1 t)
  where rn <> 1;

  dbms_output.put_line(revisions.count || ' records found from table2 to be deleted');
  delete from table2 where rev in (select column_value from table(revisions));
  dbms_output.put_line('deleted from table2');
End;

Wernfried Domscheit · Accepted Answer · 2015-01-13 08:04:14Z

3

It must be this one:

delete from table2 where rev MEMBER OF revisions;

or 

delete from table2 where rev =ANY (SELECT COLUMN_VALUE FROM TABLE(revisions));

or 

forall i in revisions.FIRST..revisions.LAST
delete from table2 where rev = revisions(i);

or 

delete from table2 where rev =ANY (
select rev 
from (
    select t.rev, row_number() over (partition by 
        column1,
        column2
        order by column3) rn
    from table1 t)
  where rn <> 1
);

or (not the preferred way of doing it)

for i in revisions.FIRST..revisions.LAST LOOP
   delete from table2 where rev = revisions(i);
END LOOP;

As already written by Marcin Wroblewski, the nested table has to be created on Schema level.

edited Jan 13, 2015 at 8:04

answered Jan 13, 2015 at 7:59

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Comments

Lalit Kumar B · Accepted Answer · 2015-01-13 08:01:41Z

1

And why would you do it in PL/SQL when it could be done in plain SQL? You are unnecessarily introducing context switching between both the engines.

All that PL/SQL code is nothing but a simple DELETE statement -

DELETE FROM table2 WHERE rev IN(
   SELECT rev FROM(
      select t.rev, row_number() over (partition by 
              column1,
              column2
              order by column3) rn
      from table1 t)
  where rn <> 1
);

answered Jan 13, 2015 at 8:01

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Collectives™ on Stack Overflow

oracle bulk delete : delete from table where column in array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related