Learn how to use SQL ALTER TABLE statement with the explained example of this tutorial. Add, delete or modify the table columns using the SQL ALTER TABLE statement.
You can add more columns to the table or you can change the name of the column. In addition to this, you can also delete the column using the SQL ALTER TABLE statement.
This is the single statement by which you can perform multiple tasks with the column of the table.
How to Use SQL ALTER TABLE Statement to Add, Delete or Modify Columns
You just need to specify the table name and the columns you want to delete or change using the ALTER table method. Also, you can rename the name of the columns using this statement. The method is very useful in all respect to manipulate the column of a table.
ADD Column Using SQL ALTER TABLE Statement
Add more columns to a table using the syntax given below. The syntax contains the table name and the column name with the data type. You have to use the ADD statement after the table name. Then after you have to specify the data type of the column.
Syntax
|
1 2 |
ALTER TABLE tablename ADD column_name data_type; |
DROP Column Using SQL ALTER TABLE Statement
Delete the column of the table using the DROP COLUMN statement with the ALTER statement. In this, you have to specify the table name from which you want to remove the column. Also, you have to specify the column name which you want to delete from the specified table.
Syntax
|
1 2 |
ALTER TABLE tablename DROP COLUMN column_name; |
MODIFY Column Using SQL ALTER TABLE Statement
Change the name of the column using the syntax given below. You need to only specify the table name and after this, you have to use the MODIFY COLUMN statement. Also, specify the column name with the data type to which you want to modify the column.
Syntax
|
1 2 |
ALTER TABLE tablename MODIFY COLUMN column_name data_type; |
RENAME Column Using SQL ALTER TABLE Statement
Rename the column name of the table using the syntax given below. To change the column name you have to specify the old column and the new column name to which you want to change the column name.
Syntax
|
1 2 |
ALTER TABLE tablename RENAME COLUMN old_column_name TO new_column_name; |
Parameter Descriptions
| Sr.No. | Parameter Name | Description |
|---|---|---|
| 1 | new_tablename | Put new table name to which you want to manipulate the column. |
| 2 | old_column_name | Enter the name of the old column which you want to modify, delete or change. |
| 3 | data_type | Specify the data type of the new column name that you want to add or modify on the selected table. |
Examples to Use SQL ALTER TABLE Statement
|
1 2 |
ALTER TABLE Employee ADD Address vachar(255); |
The above example adds one column to the table Employee. I have also specified the table name to which I want to add the column.
|
1 2 |
ALTER TABLE Employee DROP COLUMN Address; |
The above example deletes the Address column from the specified table name.
|
1 2 |
ALTER TABLE Employee MODIFY COLUMN Address character(100); |
The above example changes the data type of the Address column from varchar to character.
|
1 2 |
ALTER TABLE Employee RENAME COLUMN Address TO Place; |
The above example changes the column name from Address to Place.
You must also learn.
Reference