java2db

Alter table to add and drop the column or columns



To add new columns or remove the existing columns  of a  table, use ALTER TABLE command .See the below example , that explain how to add and drop the column.

Alter table to add and drop the column Alter table to add new column:

It is possible to add one or more columns to a table using a single sql command.

Syntax:

ALTER TABLE table_name ADD (column_name  column_type  size);
ALTER TABLE table_name ADD (col_name_1 col_type size , col_name_2  col_type  size,..);

Example:

i) Add a single column :

E_NO
E_NAME
E_GENDER
E_SALARY
E_ADDRESS
E_JOIN_DATE
E_IN_TIME
E_OUT_TIME
1 Rose F 10000 street10,xxx,UK 03-MAR-2013 10-MAR-2013:10:10.0
2 Tom M 15000 street11,xxx,UK 04-MAY-2013 12-MAY-2013:10:10.0

 

ii) Add more columns :

E_NO
E_NAME
E_GENDER
E_SALARY
E_ADDRESS
E_JOIN_DATE
E_IN_TIME
E_MAIL
E_OUT_TIME
1 Rose F 10000 street10,xxx,UK 03-MAR-2013 10-MAR-2013:10:10.0
2 Tom M 15000 street11,xxx,UK 04-MAY-2013 12-MAY-2013:10:10.0

The sql statement inserted two new columns, called E_MAIL  and  E_OUT_TIME at the end of the table.

 

Alter table to add and drop the column Alter table to drop column :

Same way,it is possible to drop one or more columns.

Syntax:

ALTER TABLE table_name DROP COLUMN column_name–to drop a single column
ALTER TABLE table_name DROP (col_name_1 , col_name_2,..); — to drop more columns

Example :

E_NO
E_NAME
E_GENDER
E_SALARY
E_ADDRESS
E_JOIN_DATE
E_IN_TIME
1 Rose M 10000 street10,xxx,UK 03-MAR-2013 10-MAR-2013:10:10.0
2 Tom M 15000 street11,xxx,UK 04-MAY-2013 12-MAY-2013:10:10.0

E_MAIL , E_OUT_TIME columns dropped from the table.

Alter table to add and drop the column  Set column as unused:
i)  Set a column as an unused column :

ii) To drop all unused columns from the table :

Note : You must have the privilege to add and drop the column .




Related Posts :
Sql FOREIGN KEY constraint on table column
Sql CHECK constraint on table column
Sql NOT NULL constraint on table column
Sql UNIQUE constraint on table column
Sql PRIMARY KEY constraint on table column
Sql full outer join on tables with an example
Sql right outer join on tables with an example
Sql left outer join on tables with an example
   [will not be published]



^ <