Re: Mass modification of the data of a column

Robert wrote:
> Hi all,
>
> I would like to modify the content of a column in a large table.
>
> The column contains two distinct values : AS or AR
> and each value must be changed to OW (for AS) and RT (for AR).
>
> The table contains about 20 000 000 rows.
>
> I tried to modify the values with a PL/SQL procedure that did nearly
this :
> LOOP
> update tablename set col='OW' where col='AS' and rownum < 50000;
> IF (SQL%ROWCOUNT = 0) then
> commit;
> exit;
> end if;
> commit;
> END LOOP;
>
> If I choose a bigger value for the rownum clause, I usually raise the

> "rollback segment to small" error.
> So the procedure is very long to complete (hours or days).
>
> So, I am looking for a quicker way to make these mass
modifications...
>
> I has the idea to export the table content to a file, change the
values
> then re-import the file. Will it be quicker ? safe ?
>
> Is there a way to execute the update statement without using the
> rollback segment ?
>
> Any other ideas ?
>
> Thanks a lot in advance,
>
> Bobby

Oracle version?
Is this a one time update or on-going?

If you want to avoid massive update/deletes then one way is to create a copy of the table "create table table2 as select decode(col, 'AR', 'RT', ...),... from table1" (make sure data is modified inside the select statement). Truncate and drop table1, rename table2 to table1 and re-create all constraints on table1.

You may want to test this first before doing it in Production. Regards
/Rauf Received on Mon Jan 24 2005 - 08:13:51 CST