Hi everyone,

I have to run a conversion on multiple tables to change the data in one column on each table. I am happy to run this separately for each table, but I would like to know the best way to do this.

e.g. for table 1 with 50 colums and 1000 rows, I wish to change all the data in column 3 to new values based on a lookup spreadsheet I have, containing a list of old values and new values:

value1;value2;
value3;value4;
etc.

so I want to read the first row in the table, see the data in col3, and if it matches value1, change it value2, etc.

I want some kind of a scrip to do this so that I can then change the relevant details for the next table, and change the lookup table with the values for that table.

Does anyone have any suggestions on the most efficient way to do this please?

Many thanks.

By doing that,
1. create a lookup table with 2 columuns new and old
2. load the spreadsheet into the lookup table by using SQLloader
3. write PL/SQL
3.1 create cursor for the lookup table
3.2 wirte the cursor for loop to update the table with the new value

This piece of code can be reused for the rest of your tables.

gohome88

Add the new column in the table, update the value accordingly (cant be that hard), change the PK or UK (if it involves any old column), remove the NOT NULL property from these old columns (if still present), empty the old values, try to remove the columns (wont work on 9i I guess, but have a try).

If the old columns bother you, you can go on to step 2, trying to recreate the table without those columns (create table as select ...), and/or "rename table ...".

Hi there.