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.
Related Links
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;
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 |
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 |
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 |
Related Links