SQL ALTER TABLEStatements

ALTER TABLE Statement

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

SQL ALTER TABLE Syntax

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

To delete a column from a table, use the following syntax (note that some database systems do not allow this way of deleting a column in a database table):

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

SQL Server / MS Access：

My SQL / Oracle：

Oracle 10G and later versions:

ALTER TABLE table_name
MODIFY column_name datatype;

SQL ALTER TABLE Example

Look at the "Persons" table:

Now we want to add a column named "DateOfBirth" to the "Persons" table.

We use the following SQL statement:

Please note that the new column "DateOfBirth" has a type of date, which can store dates. The data type specifies the type of data that can be stored in the column. For a complete reference to the data types available in MS Access, MySQL, and SQL Server, visit our completeData Type Reference Manual。

Now, the "Persons" table will look like this:

Change Data Type Example

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

We use the following SQL statement:

Please note that the type of the "DateOfBirth" column is now year, which can store years in a 2-digit or 4-digit format.

DROP COLUMN Example

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

We use the following SQL statement:

Now, the "Persons" table will look like this: