SQL Alter Table
Category : TECH Author : Ankur Rastogi Date : Sun Mar 27 2016 Views : 68

Overview
SQL Alter table is used to update the database table structure of an existing table. Alter table command can add, delete or modify any of the column in an exisitng table. Lot of time we get a requirement to either add a new column, delete an existing column or change the name, place, data type or data a column can hold, In all such requirement we can use SQL Alter statement.
Syntax for SQL Alter statement for add, drop and modify a column is mentioned below in their respective orders
Syntax
ADD a new column
ALTER TABLE table_name ADD new_column_name data_type;
DROP an exiting column
ALTER TABLE table_name DROP old_column_name;
Update an exiting column
ALTER TABLE table_name MODIFY COLUMN old_column_name data_type;
In MYSQL above command can be written as
ALTER TABLE table_name MODIFY column_name data_type;
In MYSQL if we have to update an exiting column by changing its name also then the command is
ALTER TABLE table_name change old_column_name new_column_name data_type;
Example
Let us have a better understanding with the help of an example
Consider table hostel_list_archive that we created in our previous step
+----+-------------------+-----------+----------+
| id | name | occupancy | capacity |
+----+-------------------+-----------+----------+
| 1 | ASHOK BHAVAN | 2 | 1090 |
| 2 | KAILASH BHAVAN | 2 | 990 |
| 3 | RUDRAKSH BHAVAN | 2 | 990 |
| 4 | KAVERI BHAVAN | 1 | 690 |
| 5 | BHAGIRATHI BHAVAN | 4 | 2140 |
| 6 | NARMADA BHAVAN | 6 | 3240 |
| 7 | GANGA BHAVAN | 1 | 780 |
| 8 | YAMUNA BHAVAN | 8 | 4580 |
+----+-------------------+-----------+----------+
Example Case 1:
Let us first add new column new_col varchar(100), SQL query for that will be
ALTER TABLE hostel_list_archive ADD new_col varchar(100) default 'TEST VAL';
+----+-------------------+-----------+----------+----------+
| id | name | occupancy | capacity | new_col |
+----+-------------------+-----------+----------+----------+
| 1 | ASHOK BHAVAN | 2 | 1090 | TEST VAL |
| 2 | KAILASH BHAVAN | 2 | 990 | TEST VAL |
| 3 | RUDRAKSH BHAVAN | 2 | 990 | TEST VAL |
| 4 | KAVERI BHAVAN | 1 | 690 | TEST VAL |
| 5 | BHAGIRATHI BHAVAN | 4 | 2140 | TEST VAL |
| 6 | NARMADA BHAVAN | 6 | 3240 | TEST VAL |
| 7 | GANGA BHAVAN | 1 | 780 | TEST VAL |
| 8 | YAMUNA BHAVAN | 8 | 4580 | TEST VAL |
+----+-------------------+-----------+----------+----------+
Example Case 2:
Now let us see how to drop this new_col column, SQL Query for that will be
ALTER TABLE hostel_list_Archive DROP new_col;
+----+-------------------+-----------+----------+
| id | name | occupancy | capacity |
+----+-------------------+-----------+----------+
| 1 | ASHOK BHAVAN | 2 | 1090 |
| 2 | KAILASH BHAVAN | 2 | 990 |
| 3 | RUDRAKSH BHAVAN | 2 | 990 |
| 4 | KAVERI BHAVAN | 1 | 690 |
| 5 | BHAGIRATHI BHAVAN | 4 | 2140 |
| 6 | NARMADA BHAVAN | 6 | 3240 |
| 7 | GANGA BHAVAN | 1 | 780 |
| 8 | YAMUNA BHAVAN | 8 | 4580 |
+----+-------------------+-----------+----------+
Example Case 3:
Now let us see how we can update an existing column, SQL query will be
ALTER TABLE hostel_list_archive MODIFY COLUMN name varchar(300);
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id | int(2) | NO | | 0 | |
| name | varchar(300) | YES | | NULL | |
| occupancy | tinyint(1) | YES | | NULL | |
| capacity | int(5) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+

Overview
SQL Alter table is used to update the database table structure of an existing table. Alter table command can add, delete or modify any of the column in an exisitng table. Lot of time we get a requirement to either add a new column, delete an existing column or change the name, place, data type or data a column can hold, In all such requirement we can use SQL Alter statement.
Syntax for SQL Alter statement for add, drop and modify a column is mentioned below in their respective orders
Syntax
ADD a new column
ALTER TABLE table_name ADD new_column_name data_type;
DROP an exiting column
ALTER TABLE table_name DROP old_column_name;
Update an exiting column
ALTER TABLE table_name MODIFY COLUMN old_column_name data_type;
In MYSQL above command can be written as
ALTER TABLE table_name MODIFY column_name data_type;
In MYSQL if we have to update an exiting column by changing its name also then the command is
ALTER TABLE table_name change old_column_name new_column_name data_type;
Example
Let us have a better understanding with the help of an example
Consider table hostel_list_archive that we created in our previous step
+----+-------------------+-----------+----------+
| id | name | occupancy | capacity |
+----+-------------------+-----------+----------+
| 1 | ASHOK BHAVAN | 2 | 1090 |
| 2 | KAILASH BHAVAN | 2 | 990 |
| 3 | RUDRAKSH BHAVAN | 2 | 990 |
| 4 | KAVERI BHAVAN | 1 | 690 |
| 5 | BHAGIRATHI BHAVAN | 4 | 2140 |
| 6 | NARMADA BHAVAN | 6 | 3240 |
| 7 | GANGA BHAVAN | 1 | 780 |
| 8 | YAMUNA BHAVAN | 8 | 4580 |
+----+-------------------+-----------+----------+
Example Case 1:
Let us first add new column new_col varchar(100), SQL query for that will be
ALTER TABLE hostel_list_archive ADD new_col varchar(100) default 'TEST VAL';
+----+-------------------+-----------+----------+----------+
| id | name | occupancy | capacity | new_col |
+----+-------------------+-----------+----------+----------+
| 1 | ASHOK BHAVAN | 2 | 1090 | TEST VAL |
| 2 | KAILASH BHAVAN | 2 | 990 | TEST VAL |
| 3 | RUDRAKSH BHAVAN | 2 | 990 | TEST VAL |
| 4 | KAVERI BHAVAN | 1 | 690 | TEST VAL |
| 5 | BHAGIRATHI BHAVAN | 4 | 2140 | TEST VAL |
| 6 | NARMADA BHAVAN | 6 | 3240 | TEST VAL |
| 7 | GANGA BHAVAN | 1 | 780 | TEST VAL |
| 8 | YAMUNA BHAVAN | 8 | 4580 | TEST VAL |
+----+-------------------+-----------+----------+----------+
Example Case 2:
Now let us see how to drop this new_col column, SQL Query for that will be
ALTER TABLE hostel_list_Archive DROP new_col;
+----+-------------------+-----------+----------+
| id | name | occupancy | capacity |
+----+-------------------+-----------+----------+
| 1 | ASHOK BHAVAN | 2 | 1090 |
| 2 | KAILASH BHAVAN | 2 | 990 |
| 3 | RUDRAKSH BHAVAN | 2 | 990 |
| 4 | KAVERI BHAVAN | 1 | 690 |
| 5 | BHAGIRATHI BHAVAN | 4 | 2140 |
| 6 | NARMADA BHAVAN | 6 | 3240 |
| 7 | GANGA BHAVAN | 1 | 780 |
| 8 | YAMUNA BHAVAN | 8 | 4580 |
+----+-------------------+-----------+----------+
Example Case 3:
Now let us see how we can update an existing column, SQL query will be
ALTER TABLE hostel_list_archive MODIFY COLUMN name varchar(300);
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id | int(2) | NO | | 0 | |
| name | varchar(300) | YES | | NULL | |
| occupancy | tinyint(1) | YES | | NULL | |
| capacity | int(5) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
Disclaimer: The above content reflect author’s personal views and do not reflect the views of OYEWIKI. Neither OYEWIKI nor any person/organization acting on its behalf is liable to accept any legal liability/responsibility for any error/mislead in this information or any information available on the website. This website in no way accepts the responsibility for any loss, injury, damage, discomfort or inconvenience caused as a result of reliance on any information provided on this website.
If you want to add more comments to the article or you see any thing incorrect please write a comment below and we will surely get back to you.