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

Search Keys

  • alter table modify column name
  • how to rename table in sql
  • alter table add comment
  • alter table column size
  • alter table nullable
  • alter table not null
  • add column to existing table
  • alter table insert column
  • alter table command
  • add new column to table
  • alter table change data type
  • add column to table oracle
  • alter table edit column
  • alter table remove column oracle
  • alter table change data type sql server
  • alter table field
  • sql alter table datatype
  • sql change table
  • change column type
  • change column name sql
  • how to change column name in sql
  • alter add column
  • sql server alter column type
  • alter table sql server example