Hi!
You could easily use rownum for doing this kind of update... Just do a
commit after each update.
SQL create table u (a number);
Table created.
SQL alter table u modify a constraint un not null;
Table altered.
SQL insert into u values (0);
1 row created.
SQL insert into u values (0);
1
Ryan is right do this:
DECLARE
TYPE myRef IS REF CURSOR;
myCursormyRef;
TYPE myType IS TABLE OF Table_Name.PKid%TYPE INDEX BY BINARY
INTEGER;
p_limit PLS_INTEGER := 1000;
P_PKid myType;
bulk collect the flag into a pl/sql table. forall with a limit clause and
then commit after hitting each limit.
this is on asktom.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, July 08, 2003 7:09 PM
I have a table of about one million
huh???
--- Ryan [EMAIL PROTECTED] wrote:
bulk collect the flag into a pl/sql table. forall with a limit clause
and then commit after hitting each limit.
this is on asktom.
anything more down the earth for me please?
thx
maa
- Original Message -
To: Multiple recipients of list
uhhh... this is pretty easy.
declare
type mytable is table of Whatever
l_table mytable;
l_updatevalue mytable
begin
select rowid, updateValue
bulk collect into
l_table;
from table;
now update off the rowid for your value.
go through like 5000 records in the pl/sql table
:
Sent by: Subject: Re: update about 100K records
[EMAIL PROTECTED
]
.comcc:
Sent by: Subject: Re: update
about 100K records
--- Ryan [EMAIL PROTECTED] wrote:
uhhh... this is pretty easy.
declare
type mytable is table of Whatever
should I really put whatever, or table%rowtype?
l_table mytable;
l_updatevalue mytable
are both variables of the same type?
begin
select rowid, updateValue
do you
if your in 8i you can only use one field for a bulk collect( i think ic ant
remember). I think they fixed that in 9i.
i meant whatever.
i also 'think' to do a bulk collect in 8i you cant anchor the pl/sql table.
has to be varchar2, number, etc... im pretty sure you can do it with a
rowid. I
Actually, we just upgraded to 8.0
I guess this lowers my options, doesnt it?
I'm 30. Not quite senile. Sorry.
maa
--- Ryan [EMAIL PROTECTED] wrote:
if your in 8i you can only use one field for a bulk collect( i think
ic ant
remember). I think they fixed that in 9i.
i meant whatever.
i
?
MaryAnn Atkinson
[EMAIL PROTECTED]To: Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
.comcc:
Sent by: Subject: Re: update
about 100K records
Maryann Atkinson wrote:
I have a table of about one million records.
About 100,000 of them have a flag which I need it set to 0.
Because of the size of the rollback segment, I cannot
update them all and then commit, I need to do it in
sets of 1000 records.
Do I need a cursor for
12 matches
Mail list logo