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
——— —- ————
TEST_NAME VARCHAR2(15)
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.
Syntax
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;
e.g.
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!
