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

• How to rename table using ALTER TABLE and
• How To add / delete a column from an existing table using ALTER TABLE.

In this tutorial we will learn how to rename column/s of a table as well as how to modify one or more columns of a table.

How To Rename A Column Of A Table

Syntax 

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 as well.

Table test has only one column test_name. Structure of table Test is as follows: 

DESC test
Name    Null   Type 
——-    —-    ——— 
TEST_ID       NUMBER(3)

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 out 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 learn how to modify the column definition of a table. What I mean by modifying the column definition is to change the Data type and column width of the column. 

Syntax

ALTER TABLE  table_name  MODIFY  colun_name  data_type (size);

Example:

Above we renamed column test_id to col1. Column col1 has data type NUMBER 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. We can do that by writing the following code:

ALTER TABLE  test  MODIFY  col1  VARCHAR2(30);

You can verify the change by describing the structure of the test table using 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 network and help me spread the word. Thanks & have a great day!