SQL ALTER TABLE Statement
The SQL ALTER TABLE statement is used to add new fields, modify fields, or delete fields in an existing database table.
SQL ALTER TABLE Syntax
There are various type of commands available in ALTER TABLE statement.
To add a new field to an existing table, use the following syntax:
ALTER TABLE existing_table_name
ADD column_name1 datatype;
To remove or drop a existing field from an existing table, use the following syntax:
ALTER TABLE existing_table_name
DROP COLUMN column_name1;
To change or modify the data type of a existing field from an existing table, use the following syntax:
For MySql
ALTER TABLE existing_table_name
MODIFY COLUMN column_name datatype;
For Microsoft SQL Server / Microsoft Access
ALTER TABLE existing_table_name
ALTER COLUMN column_name datatype;
Search Keys
- sql alter table statement
- alter table add column
- alter table
- alter table rename column
- alter table add foreign key
- alter table rename
- alter table foreign key
- sql rename table
- alter table primary key
- t sql alter table add column
- alter table change column type
- sql add column to table
- alter table drop column oracle
- alter table modify column
- alter table syntax
- sql alter table add column
- alter table modify column oracle
- alter table add constraint
- alter table alter column
- oracle alter table drop column
- alter table delete column
- alter table remove column
- modify table
- sql update table add column
Sample Database Table - Employee
ID |
EName |
EAge |
ECity |
EDept |
111 |
Suresh Babu |
32 |
Nasik |
Sql Programming |
222 |
Siva Kumar |
22 |
Chennai |
Database Administrator |
333 |
Suresh Kumar |
33 |
Nasik |
Sql Hacker |
444 |
Bala Murugan |
20 |
Madurai |
Database Administrator |
555 |
Haris Karthik |
25 |
Bangalore |
Sql Programming |
666 |
Varshith Kutty |
28 |
Madurai |
Sql Programming |
SQL ALTER TABLE Example - Add New Column
Now we want to add a new field named "ESalary" in the "Employee" table.
We use the below SQL statement:
ALTER TABLE Employee
ADD ESalary INT;
Notice that the new field, "ESalary", is of type int and is going to hold a integer value.
After executing above query, the "Employee" table will now look like this:
ID |
EName |
EAge |
ECity |
EDept |
ESalary |
111 |
Suresh Babu |
32 |
Nasik |
Sql Programming |
|
222 |
Siva Kumar |
22 |
Chennai |
Database Administrator |
|
333 |
Suresh Kumar |
33 |
Nasik |
Sql Hacker |
|
444 |
Bala Murugan |
20 |
Madurai |
Database Administrator |
|
555 |
Haris Karthik |
25 |
Bangalore |
Sql Programming |
|
666 |
Varshith Kutty |
28 |
Madurai |
Sql Programming |
|
SQL ALTER TABLE Example - Change Column Datatype
Now we want to change or modify the datatype of the field named "ESalary" from INT type to DECIMAL type in the "Employee" table.
We use the below SQL statement:
ALTER TABLE Employee
ALTER COLUMN ESalary DECIMAL(8,2);
Notice that the "ESalary" field is now of type DECIMAL and is going to hold a integer and floating-point value.
SQL ALTER TABLE Example - Remove Column
Now we want to remove or delete the field named "ESalary", in the "Employee" table.
We use the below SQL statement:
ALTER TABLE Employee
DROP COLUMN ESalary;
After executing above query, the "Employee" table will now look like this:
ID |
EName |
EAge |
ECity |
EDept |
111 |
Suresh Babu |
32 |
Nasik |
Sql Programming |
222 |
Siva Kumar |
22 |
Chennai |
Database Administrator |
333 |
Suresh Kumar |
33 |
Nasik |
Sql Hacker |
444 |
Bala Murugan |
20 |
Madurai |
Database Administrator |
555 |
Haris Karthik |
25 |
Bangalore |
Sql Programming |
666 |
Varshith Kutty |
28 |
Madurai |
Sql Programming |