How to rename and modify a column using SQL ALTER TABLE.

By Manish Sharma

Rename and Modify column of a table.

Rename and Modify column of a table using SQL alter table statement Explained in Detail

This is the third tutorial in ALTER table series. Till now in previous tutorials we have seen

  1. How to rename table using ALTER TABLE and
  2. How To add / delete a column from an existing table using ALTER TABLE.
In this tutorial we will see how to rename a column/s of a table and how to modify one or more columns of a table.

How to rename a column of a table.

 ALTER TABLE  table_name  RENAME COLUMN  old_name_of_column  TO  new_name_of_column;

For example
In the previous tutorial we have created a simple table by the name of TEST so we will be using that for this example.
Table test has only one column test_name.
Structure of table Test is as follows:

DESC test
Name    Null   Type
-------    ----    ---------
Say you want to rename the column test_id to col1

ALTER TABLE  test  RENAME COLUMN  test_id  TO  col1;
On executing the above query the column test_id of table test will be renamed to col1. You can check it by describing the structure of TEST table.

DESC test
Name    Null   Type
-------    ----    ---------
COL1       NUMBER(3)

How To modify the column definition using ALTER TABLE.

Above we saw how to rename a column of a table. Now we will see how to modify the column definition of a table.
By modifying the column definition, what I mean is to change the Data type and column width of the column.

 ALTER TABLE  table_name  MODIFY  colun_name  data_type (size);

Above we renamed column test_id to col1. Column col1 has NUMBER data type and column width 3;
Now let’s say we want to change the data type of column col1 from NUMBER to VARCHAR2 and data width from 3 to 30;

ALTER TABLE  test  MODIFY  col1  VARCHAR2(30);
You can verify the change by describing the structure of the test table by DESCRIBE command

DESC test
Name    Null   Type
-------    ----    ---------
COL1        VARCHAR2(30)

That’s it. This is the basic concept behind renaming and modifying a column of a Table. Keep checking for more such simple and easy to understand concepts. Also please do like and share this on your social networking and help me spread the word. Thanks!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn