YouTip

# 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: ALTER TABLE table_name ADD column_name datatype To delete a column in a table, use the following syntax (please note that some database systems do not allow this way of deleting a column in a database table): 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 column_name datatype Oracle 10G and later versions: ALTER TABLE table_name MODIFY column_name datatype; * * * ## SQL ALTER TABLE Examples Look at 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" in the "Persons" table. We use the following SQL statement: ALTER TABLE Persons ADD DateOfBirth date Please note that the new column "DateOfBirth" is of type date and can hold a date. The data type specifies what kind of data can be held in a column. To learn about the available data types in MS Access, MySQL, and SQL Server, please visit our complete (#). 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 | | * * * ## 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: ALTER TABLE Persons ALTER COLUMN DateOfBirth year Please note that now the "DateOfBirth" column is of type year and can hold 2 or 4 digit year formats. * * * ## DROP COLUMN Example Next, we want to delete the "DateOfBirth" column in the "Persons" table. We use the following SQL statement: ALTER TABLE Persons DROP COLUMN DateOfBirth 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 |