What is the SQL ALTER TABLE Statement

SQL ALTER TABLE Statement

The ALTER TABLE statement is used Modify the structure of a Table. In general we can use Alter Table statement  to add, delete, or modify columns in an existing table.

Syntax:

To add a column in a table, use the following syntax:

ALTER TABLE table_name ADD column_name datatype

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name DROP COLUMN column_name

To change the data type of a column in a 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_name datatype

To add a column named "DateOfBirth" in the "Customers" table :

ALTER TABLE Customers ADD DateOfBirth date

Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. 

  

Change Data Type :
Now we want to change the data type of the column named "DateofBirth" in the "Customers" table.we use the following SQL statement:

 ALTER TABLE Customers ALTER COLUMN DateOfBirth year

Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two-digit or four-digit format.

DROP COLUMN :

Next, we want to delete the column named "DateOfBirth" in the "Customers" table.

We use the following SQL statement:

ALTER TABLE Customers DROP COLUMN DateOfBirth

Thanks, TAMATAM