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!