SQL ALTER TABLE Statement

SqlALTER 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 columns to a table, use the following syntax:

ALTER TABLE table_name ADD column_name datatype

To delete a column from a table, use the following syntax (note that some database systems do not allow this way to delete columns 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  COLUMN column_name datatype

Oracle 10G Post-release:

ALTER TABLE table_namemodify column_name datatype;

SQL ALTER TABLE Instance

Please see the "Persons" table:

City

p_id	LastName	FirstName	Address
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

Note that the type of the new column "dateOfBirth" is date and can hold the date. The data type specifies the type of data that can be stored in the column. To learn about the types of data available in MS access, MySQL, and SQL Server, please visit our complete data type reference manual.

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

City

p_id	LastName	FirstName	Address		dateOfBirth
1	Hansen	Ola	TIMOTEIVN 10	Sandnes
2	Svendson	Tove	BORGVN 23	Sandnes
3	Pettersen	Kari	STORGT 20	Stavanger

Changing data type instances

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  Year

Note that the type of the "dateOfBirth" column is now year and can hold 2-bit or 4-bit formats.

DROP COLUMN Instance

Next, we want to delete the "dateOfBirth" column in the "Person" table.

We use the following SQL statement:

ALTER TABLE Persons DROP COLUMN dateOfBirth

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

City

p_id	LastName	FirstName	Address
1	Hansen	Ola	TIMOTEIVN 10	Sandnes
2	Svendson	Tove	BORGVN 23	Sandnes
3	Pettersen	Kari	STORGT 20	Stavanger

SQL ALTER TABLE Statement