Change the Column Datatype using SQL

Follow the simple steps to change the column datatype which is also having a data inside.
Normally, it’s not possible to change the column datatype with the existing data.

Steps:

1. Add New temp Column with desired datatype.

SQL> Alter Table <table_name>
Add temp datatype(size);

2. Update the data into new column.

SQL> Update <table_name>
set temp = <original_columnname>
where <original_columnname> is not null;

3. Drop the original Column.

SQL> Alter table <table_name>
Drop column <original_columnname>;

4. Rename the new column to original Name.

SQL> Alter table <table_name>
rename column temp to <original_columnname>;

Note:- change the table_name, datatype, Original_columnname and size