SQL ALTER TABLE statement

ALTER TABLE statement

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

SQL ALTER TABLE syntax

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

ALTER TABLE table_name
ADD column_name datatype

To delete a column from a table, please use the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name

Note:Certain database systems do not allow this method of deleting a column from a database table (DROP COLUMN column_name).

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

ALTER TABLE table_name
ALTER COLUMN column_name datatype

The original table (used in the example):

Persons Table:

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

SQL ALTER TABLE Example

Now, we want to add a new column named 'Birthday' to the 'Persons' table.

We use the following SQL statements:

ALTER TABLE Persons
ADD Birthday date

Please note that the new column 'Birthday' is of type date, which can store dates. The data type specifies the type of data that can be stored in the column.

The new 'Persons' table will look like this:

Id LastName FirstName Address City Birthday
1 Adams John Oxford Street London 　
2 Bush George Fifth Avenue New York 　
3 Carter Thomas Changan Street Beijing 　

Change Data Type Example

Now we want to change the data type of the 'Birthday' column in the 'Persons' table.

We use the following SQL statements:

ALTER TABLE Persons
ALTER COLUMN Birthday year

Please note that the data type of the 'Birthday' column is year, which can store years in 2-digit or 4-digit format.

DROP COLUMN Example

Next, we will delete the 'Birthday' column from the 'Person' table:

ALTER TABLE Person
DROP COLUMN Birthday

The 'Persons' table will become like this:

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing