SQL ALTER TABLE Statement

The 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 in a table, use the following syntax:

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

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

SQL Server / MS Access:

My SQL / Oracle (prior version 10G):

Oracle 10G and later:

SQL ALTER TABLE Example

Look at the "Persons" table:

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

We use the following SQL statement:

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. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.

The "Persons" table will now look like this:

Change Data Type Example

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

We use the following SQL statement:

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 Example

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

We use the following SQL statement:

The "Persons" table will now look like this: