SQL ALTER TABLE statement

ALTER TABLE statement

ALTER TABLE statements are used to add, remove, or modify columns from existing tables.

SQL ALTER TABLE syntax

To add columns to a table, use the following syntax: 

ALTER TABLE table_name                
ADD column_name datatype

To delete columns from a table, use the following syntax (note that some database systems do not allow columns in the database table to be deleted in this way): 

ALTER TABLE table_name                
DROP COLUMN column_name

To change the data type of the columns in the table, use the following syntax:

SQL Server / MS Access： 

ALTER TABLE table_name                
ALTER COLUMN column_name datatype

My SQL / Oracle： 

ALTER TABLE table_name                
MODIFY COLUMN column_name datatype

SQL ALTER TABLE instance

Look at the "Peoples" table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Now, we'd like to add a column called DateOfBirth to the Peoples table.

Let's use the following SQL statement: 

ALTER TABLE Persons                
ADD DateOfBirth date

Note that the new column "DateOfBirth" is of type date and can hold dates. T he data type specifies the type of data that can be stored in the column. For the types of data available in MS Access, MySQL, and SQL Server, visit our complete Data Type Reference Manual.

The Persons table will now look like this:

P_Id LastName FirstName Address City DateOfBirth
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Change the instance of the data type

Now we want to change the data type of the DateOfBirth column in the Peoples table.

Let's use the following SQL statement: 

ALTER TABLE Persons                
ALTER COLUMN DateOfBirth year

Note that the type of "DateOfBirth" column is now year and can hold years in 2- or 4-bit formats.

DROP COLUMN instance

Next, we want to remove the DateOfBirth column from the Person table.

Let's use the following SQL statement: 

ALTER TABLE Persons                
DROP COLUMN DateOfBirth

The Persons table will now look like this:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger