SQL Tutorial SQL Advanced SQL Database SQL References

SQL ALTER COLUMN Keyword



The SQL ALTER COLUMN keyword is used to change the data type of a column in an existing table.

Syntax

The syntax for using ALTER COLUMN statement to modify datatype of a column is given below:

/* SQL Server / MS Access */
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

/* MySQL / Oracle (prior 10G version) */
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

/* Oracle 10G and later */
ALTER TABLE table_name
MODIFY column_name datatype;

Example:

Consider a database table called Employee with the following records:

EmpIDNameCityAge
1JohnLondon25
2MarryNew York24
3JoParis27
4KimAmsterdam30

In this table, the datatype of City column is VARCHAR(100). To change the datatype of the column to VARCHAR(255), the SQL code is given below:

/* SQL Server / MS Access */
ALTER TABLE Employee
ALTER COLUMN City VARCHAR(255);

/* My SQL / Oracle (prior 10G version) */
ALTER TABLE Employee
MODIFY COLUMN City VARCHAR(255);

/* Oracle 10G and later */
ALTER TABLE Employee
MODIFY City VARCHAR(255);

This will change the datatype of City column from VARCHAR(100) to VARCHAR(255). For a complete reference of all the data types available in SQL database, see Data Types reference.


❮ SQL Keywords

5