How to add column to an existing table using SQL ALTER TABLE statement.

By Manish Sharma

Add column to an existing Table.

Add column to an existing table using SQL alter table statement Explained in Detail

This is the second tutorial in the alter table series. In the last tutorial we saw How to rename a table using ALTER TABLE statement.
Has it ever happened with you that after creating a table, you realized that you need to add another column to it? It generally happens with me.
The first thing which comes into the mind in this scenario is to Drop the table and recreate it with that extra column. But what if your table has a data which you cannot compromise. Means dropping a table is not the solution here.
Don’t worry there is a solution for this problem.

It is possible to add a column to an existing table using ALTER TABLE statement. By Using ALTER TABLE statement you can add one or more than one columns to an existing table.
Let’s see how.

In the previous tutorial we have created a very simple table by the name of Test. Test table has only one column test_name.

DESC test
Name     Null   Type
---------     ----   ------------
Say you want to add another column test_id to this table. Test_id column will be of NUMBER data type and column width (size of column) will be 3.

Let’s see how to add test_id column to Test table using alter table statement.

In order to add test_id column to our existing table Test using alter table we have to first understand the syntax of the alter table statement.
ALTER TABLE  existing_table_name  ADD  new_column_name  data_type (size);

Note here we didn’t write "COLUMN" after key word ADD.
Do not write ADD COLUMN after existing table name otherwise Oracle will raise ORA-00904: : invalid identifier SQL error.

Hope syntax is clear. So now let’s write a SQL query for adding test_id column to Test table.

ALTER TABLE   test   ADD   test_id   NUMBER(3);
On executing the above query you will get a test_id column in the already created table Test.

How To add Multiple Columns In a Table.

Similarly you can add multiple columns to an existing column. For that you just have to put your columns definitions in a parenthesis after Keyword ADD.
Syntax for adding multiple columns to an existing table

ALTER TABLE existing_table_name
   ADD (
    column_name1   DataType(size),
     column_name2   DataType(size),
     column_name n   DataType(size)

How to drop a column in a table.

Similarly you can drop a column from your existing table using alter table command. Say you want to drop test_name column from Test table.
Syntax ALTER TABLE  existing_table_name  DROP COLUMN  column_name;
ALTER TABLE  test  DROP COLUMN  test_name;

So this is it folks. Hope you find this article useful. Stay tuned for more such tutorials. You can also check out my YouTube channel for more SQL tutorial videos. Please share it with your friends 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