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 to you that after creating a table, you realized that you need to add another column to it? It usually happens with me. 

The first thing which comes to the mind in this scenario is to Drop the table altogether and recreate it with that extra column. But what if your table has a data which you cannot compromise. This means that dropping the table is not an option 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 columns to an existing table.
Let’s see how.

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

DESC test
Name     Null   Type 
———     —-   ———— 

Say you want to add another column test_id to this table. Test_id column has to be of NUMBER data type with a column width (size of column) of 3.

Now, let’s see how we can 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 the key word ADD.

Never write ADD COLUMN after existing table name otherwise Oracle will raise ORA-00904: : invalid identifier SQL error.

Hope the syntax is clear to you. 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 table. For that you just have to put your columns definitions in a parenthesis after Keyword ADD.

The syntax for adding multiple columns to an existing table is –

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

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 for this is –

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 free SQL tutorial videos. Please share it with your friends and help me spread the word. Thanks & have a great day!