Easy Tutorial
Sql Having Sql Func Mid Sql Between Sql Drop Sql Wildcards Sql Join Inner Sql Join Full Sql Func Lcase Sql Where Sql Join Sql Func Round Sql Primarykey Sql Func First Sql Func Len Sql Distinct Sql Func Ucase Sql Function Sql Delete Sql Autoincrement Func Curtime Sql Update Sql Isnull Func Date Sub Sql Constraints Sql Intro Sql Dates Sql Select Into Sql Hosting Sql Create Table Sql Func Format Sql View Sql Func Count Sql Index Sql Like Sql Datatypes General Sql Datatypes Func Convert Sql Func Avg Sql Foreignkey Sql Func Sum Sql Join Right Func Curdate Sql Alias Sql Union Func Date Add Sql Groupby Sql Summary Sql In Sql Null Values Sql Syntax Sql Tutorial Sql Insert Into Select Sql And Or Sql Join Left Sql Func Now Func Date Sql Top Index Sql Create Db Sql Unique Sql Check Func Extract Sql Alter Sql Notnull Func Now Sql Select Sql Insert Sql Func Max Sql Exists Sql Func Min Func Date Format Func Datediff Mysql Func Getdate Sql Orderby Func Datediff Sql Func Last Sql Create Index Func Dateadd Sql Quickref Func Datepart
❮ Func Extract Sql Notnull ❯

SQL ALTER TABLE Statement

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 (note that some database systems do not allow deleting columns in this way):

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

SQL Server / MS Access:

MySQL / Oracle:

Oracle versions after 10G:

ALTER TABLE table_name
MODIFY column_name datatype;

SQL ALTER TABLE Example

Consider the "Persons" 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 want to add a column named "DateOfBirth" to the "Persons" table.

We use the following SQL statement:

Note that the new column "DateOfBirth" is of type date, which can store dates. The data type specifies the type of data that can be stored in a column. For information on available data types in MS Access, MySQL, and SQL Server, visit our complete Data Types Reference.

Now, the "Persons" table will 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

Changing 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:

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

DROP COLUMN Example

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

We use the following SQL statement:

Now, the "Persons" table will 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
❮ Func Extract Sql Notnull ❯