SQL ALTER TABLE statement

ALTER TABLE statement

ALTER TABLE statement is used in an existing table to add, delete, or modify columns.

SQL ALTER TABLE Syntax

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

To delete columns in a table, use the following syntax (Note that some database systems do not allow this to delete a column in a database table mode):

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

SQL Server / MS Access:

My SQL / Oracle:

SQL ALTER TABLE examples

See "Persons" table:

Now, we want to add a column called "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

Note that the new column "DateOfBirth" type is date, the date can be stored. Type specifies the type of data can be stored in a column of data. For MS Access, MySQL and SQL Server data types available, please access our complete Data Types Reference Manual .

Now, "Persons" table would be as follows:

Change the data type instances

Now, we want to change the "Persons" data type table "DateOfBirth" column.

We use the following SQL statement:

Please note that now "DateOfBirth" type column is year, can hold two or four year format.

Examples of DROP COLUMN

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

We use the following SQL statement:

Now, "Persons" table would be as follows: