SELECT INTO the same table

Question

I have a requirement where I want to rename a column. In in this case, it should happen like:

1.) I need to create a new column with the existing column structure. 2.) Migrate the data to the new column. 3.) Retain the old column.

I don't have the new column structure and I have to take it from the old column.

TIA.

Answer 1

You don't need to select into the same table. Perform the following steps:

1. Create the new column as nullable.
2. Use an update statement to fill the data into the new column based on the values from the old column.
3. Add a non-nullable constraint, if appropriate.
4. Update all queries and code to use the values in the new column instead of the old column.
5. Remove the old column.

Answer 2

What version of Oracle? You can rename the column since 9i R2:

ALTER TABLE your_table
RENAME COLUMN old_name to new_name;

If you're set on recreating the table so you can use SELECT INTO, then you'd want to use the following steps:

1. Drop constraints related to the table(s)
2. Drop indexes related to the table(s)

3. Lock the original table:

   LOCK TABLE old_table IN EXCLUSIVE MODE;
   

4. Rename the table:

   RENAME old_table TO old_table_orig
   

5. Create the new table with the correct/updated columns

6. Run your SELECT INTO script(s) to populate the new table, handling data type changes if there are any
7. Add indexes
8. Add constraints
9. Drop the old_table_orig