Solved - Change the data type of a column which has already had data; ORA-01439: column to be modified must be empty to change datatype

Lets say you have a column, Level, in your Product table. The original data type of the column is Varchar. Now, you want to change the data type to Number.

If you simply use "ALTER TABLE Product MODIFY Level NUMBER", you will have the following error.

ORA-01439: column to be modified must be empty to change datatype

To work around this problem, following the following steps.

1. Add a temporary column to the Product table with the required new data type.

           ALTER TABLE Product ADD Level_temp NUMBER;

2. Use your data conversion logic, convert the data in column Level to NUMBER and add that value to the corresponding Level_temp column.

          UPDATE Product SET Level_temp = <the converted value> where Level = <original value>;

           If you don't need to convert the data, you can do the following.
           UPDATE Product SET Level_temp = Level;

3. Drop the Level column.

          ALTER TABLE Product DROP COLUMN Level;

4. Rename the Level_temp to Level.

          ALTER TABLE Product RENAME COLUMN Level_temp TO Level;

-----------------------------------------------------------------------------------------------------------------
Watch the blessing and loving online channel: SupremeMasterTV live


         

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.