Regarding FORALL ...DELETE [message #251955] |
Tue, 17 July 2007 04:28 |
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 |
|
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 |
|
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
|
|
|