SQL ALTER TABLE Statement

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

SQL ALTER TABLE Example - Add New Column

Now we want to add a new field named "ESalary" in the "Employee" table.

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:

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.

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.

ALTER TABLE Employee
DROP COLUMN ESalary;

After executing above query, the "Employee" table will now look like this: