SQL ALTER TABLE Statement

Submitted by admin on Tuesday, April 12, 2011 - 20:02.

The ALTER TABLE statement is used to change the structure of an existing table like adding, deleting, or modifying of column.

SQL ALTER TABLE Syntax

To add a new column to the existing table use the following syntax:

 ALTER TABLE TABLE_NAME
    ADD column_name datatype 

To remove a column from a table use the following syntax:

 ALTER TABLE TABLE_NAME
    DROP TABLE column_name 

To modify a column from a table use the following syntax:

 ALTER TABLE TABLE_NAME
    ALTER COLUMN column_name datatype 

Consider the following table for this exercise

Users

Firstname	Lastname	Salary	DeptID
John	Smith	1000	1
Mathew	Simon	3000	1
Bill	Steve	2200	1
Amanda	Rogers	1800	2
Steve	Hills	2800	2
Steve	jobs	2400	2
bill	cosby	700	3

Example # 1

ALTER TABLE Users ADD Phone INT

Result of the Query

Firstname	Lastname	Salary	DeptID
John	Smith	1000	1
Mathew	Simon	3000	1
Bill	Steve	2200	1
Amanda	Rogers	1800	2
Steve	Hills	2800	2
Steve	jobs	2400	2
bill	cosby	700	3

The resulted table is now formed with an additional column of Phone that we created with the alter table statement.

Comments