SQL Commands : ALTER

The ALTER TABLE statement to alter the structure of a table. With ALTER TABLE command we can add, rename, modify or delete columns in an existing table. The basic syntax of the ALTER TABLE statement is as follows :
ALTER TABLE table_name ADD (column_name datatype);
Note : In below examples, we are using Employee table, which we are created previously.

Adding A Column in Existing Table :

The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows :
ALTER TABLE table_name ADD (column_name datatype);
Example :
ALTER TABLE Employee ADD (Addr varchar(20));
The above statement add a column Addrof type varchar in Employee table.

Adding Multiple Columns in Existing Table :

To add multiple columns in an existing table, the syntax would be :
ALTER TABLE table_name ADD (column_name1 datatype, column_name2 datatype, column_nameN);
Example :
ALTER TABLE Employee ADD (City varchar(20), Pin varchar(20), Ph varchar(20));
The above statement add three columns namely City, Pin, Ph in Employee table.

Modify an Existing Column :

The basic syntax of an ALTER TABLE command to Modify the data type of an existing column is as follows :
ALTER TABLE table_name MODIFY column_name datatype;
For example : Suppose we want to increase the length of the column Addr from varchar(20) to varchar(30) in a table named Employee, then the statement would be :
ALTER TABLE Employee MODIFY Addr varchar(30);
The above statement will modify Addr column of Employee table. We can also change the data type of a column, for example :
ALTER TABLE Employee MODIFY Ph INT(10);
The above statement will change the data type of column Ph from VARCHAR(20) to INT(10), but remember that column must be empty, in order to change its data type.

Rename a Column :

By using ALTER command, we can also rename an existing column. The basic syntax of an ALTER TABLE command to rename a column is as follows.
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype(length);
Example :
ALTER TABLE Employee CHANGE Ph PhoneNo INT(10);
The above statement will rename the column Ph to PhoneNo of table Employee.

Drop (Delete) A Column :

ALTER command is also used to drop columns from an existing table. The basic syntax of an ALTER TABLE command to DROP COLUMN is as follows.
ALTER TABLE table_name DROP column_name;
Example :
ALTER TABLE Employee DROP City;
The above statement will drop the column City of table Employee.


Next Topic :