Home » SQL & PL/SQL » SQL & PL/SQL » Regarding FORALL ...DELETE
Regarding FORALL ...DELETE [message #251955] Tue, 17 July 2007 04:28 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I have coded a procedure like this


1  declare
  2     type v_type is table of sample.n%type;
  3             v v_type;
  4  begin
  5     select n bulk collect into v from sample;
  6             forall i in v.first..v.last
  7                delete from sample where n!=v(i);
  8* end;
SQL> /

PL/SQL procedure successfully completed.



When I query sample table no rows are there , means all rows have got deleted , but in the delete statement I am using a !(NOT) operator .

Why does this happen ?
Re: Regarding FORALL ...DELETE [message #251959 is a reply to message #251955] Tue, 17 July 2007 04:39 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
rolex.mp wrote on Tue, 17 July 2007 11:28
When I query sample table no rows are there , means all rows have got deleted , but in the delete statement I am using a !(NOT) operator .

Why does this happen ?

During the first cycle, all records get deleted but the ones with n = v(i). During the second (or third, fourth,...) cycle, the value of v(i) might have changed, and now the remaining records are deleted.

MHE

[Updated on: Tue, 17 July 2007 04:41]

Report message to a moderator

Re: Regarding FORALL ...DELETE [message #251960 is a reply to message #251955] Tue, 17 July 2007 04:39 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For each value, you delete all rows those that have not this value.
So for each value, you delete all rows with value distinct of the current one.
So if all rows don't have the same value, you delete all rows.

Regards
Michel
Previous Topic: query problem
Next Topic: select dump
Goto Forum:
  


Current Time: Tue Dec 03 20:32:24 CST 2024