SQL > ALTER TABLE > Rename Column Syntax
Sometimes we want to change the name of a column. To do this in SQL, we specify that we want to change the structure of the table using the ALTER TABLE command, followed by a command that tells the relational database that we want to rename the column. The exact syntax for each database is as follows:
In MySQL, the SQL syntax for ALTER TABLE Rename Column is,
ALTER TABLE "table_name"
Change "column 1" "column 2" ["Data Type"];
In Oracle, the syntax is,
ALTER TABLE "table_name"
RENAME COLUMN "column 1" TO "column 2";
Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:
Table Customer
| Column Name | Data Type |
| First_Name | char[50] |
| Last_Name | char[50] |
| Address | char[50] |
| City | char[50] |
| Country | char[25] |
| Birth_Date | datetime |
To rename "Address" to "Addr", we key in,
MySQL:
ALTER TABLE Customer CHANGE Address Addr char[50];
Oracle:
ALTER TABLE Customer RENAME COLUMN Address TO Addr;
SQL Server:
It is not possible to rename a column using the ALTER TABLE statement in SQL Server. Use
sp_rename instead.
The resulting table structure is:
Table Customer
| Column Name | Data Type |
| First_Name | char[50] |
| Last_Name | char[50] |
| Addr | char[50] |
| City | char[50] |
| Country | char[25] |
| Birth_Date | datetime |
To rename a column in SparkSQL or Hive SQL, we would use the ALTER TABLE Change Column command.
Next: SQL DROP COLUMN
This page was last updated on June 05, 2022.
Copyright © 2022 1keydata.com All Rights Reserved Privacy Policy About Contact
This article covers the different SQL queries to change the column type. We are going to learn how we can change the data type of the columns of the following databases:
- SQL Server 2019
- MySQL Server
- PostgreSQL
SQL query to change the column type in SQL Server database
We can use ALTER TABLE ALTER COLUMN statement to change the column type of the table. The syntax to change the column type is following:
ALTER TABLE [tbl_name] ALTER COLUMN [col_name_1] [DATA_TYPE] |
In the syntax,
- Tbl_name: Specify the table name
- Col_name: Specify the column name whose datatype you want to change. The col_name must be specified after the ALTER COLUMN keyword
- Datatype: Specify the new datatype and length of the column
For demonstration, I have created a table named tblStudent.
CREATETABLE[dbo].[tblstudent] [ [id] [INT] IDENTITY[1,1]NOTNULL, [student_code] [VARCHAR][20]NOT NULL, [student_firstname][VARCHAR][250]NOTNULL, [student_lastname] [VARCHAR][10] NOTNULL, [address] [VARCHAR][max]NULL, [city_code] [VARCHAR][20]NOTNULL, [school_code] [VARCHAR][20]NULL, [admissiondate] [DATETIME]NULL, CONSTRAINT [PK_ID]PRIMARYKEYCLUSTERED[[id]ASC] ] |
Suppose you want to change the data type of [address] from varchar[max] to nvarchar[1500]. Run the following query to change the column type.
Altertabletblstudentaltercolumnaddressnvarchar[1500] |
Verify the changes by running following script.
useStudentDB go select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name='tblStudent' |
As you can see, the column data type has been changed.
Important Notes:
- When we decrease the size of the column, the SQL Server will check the data of the table, and if the length of the data is higher than the new length, it returns the warning and terminate the statement
- When you change the datatype from nvarchar to varchar, and the column contains the Unicode string, then SQL Server returns the error and terminates the statement
- Unlike MySQL, the changing datatype of multiple columns is not allowed
- You cannot add
- NOT NULL constraint if the column contains the NULL values
- UNIQUE constraint if the column has duplicate values
SQL query to change the column type in MySQL Server
We can use ALTER TABLE MODIFY COLUMN statement to change the datatype of the column. The syntax to change the datatype of the column is following.
ALTERTABLE[tbl_name]MODIFYCOLUMN[col_name_1][DATA_TYPE], MODIFY[col_name_2] [data_type], MODIFY[col_name_3][data_type] |
In the syntax,
- Tbl_name: Specify the table name that contains the column that you want to change
- Col_name: Specify the column name whose datatype you want to change. The col_name must be specified after the MODIFY COLUMN keyword. We can change the data type of multiple columns. When we change the datatype of multiple columns, each column must be separated with a comma [,]
- Datatype: Specify the new datatype and length of the column. The datatype must be specified after the column name
For demonstration, I have created a table named tblactor in DemoDatabase. The code to create the table is the following
createtabletblactor [ actor_idint, first_namevarchar[500], first_name varchar[500], addressvarchar[500], CityIDint, lastupdatedatetime ] |
Now, let us understand the concept using a few examples.
Example 1: SQL query to change the datatype of one column
We want to change the column type of the address column from varchar[500] to TEXT datatype. Run the following query to change the datatype.
mysql>ALTERTABLEtblActorMODIFYaddressTEXT |
Run the following query to verify the changes:
As you can see, the datatype of the address column has been changed to TEXT.
Example 2: SQL query to change the datatype of multiple columns
We can change the datatype of multiple columns of a table. In our example, we want to change the column type of the first_name and last_name. The new datatype of the columns is varchar[200].
mysql>ALTERTABLEtblActorMODIFYfirst_nameTINYTEXT,modifylast_nameTINYTEXT; |
Run the following query to verify the changes:
As you can see, the datatype of the first_name and last_name column has been changed to TINYTEXT.
Example 3: Rename the column in MySQL Server
To rename the columns, we must use ALTER TABLE CHANGE COLUMN statement. Suppose you want to rename the column CityID to CityCode, you must execute the following query.
mysql>ALTERTABLEtblActorCHANGECOLUMNCityIDCityCodeint |
Run the describe command to view the changes in table structure.
As you can see, the column name has been changed.
SQL query to change the column type in PostgreSQL database
We can use ALTER TABLE ALTER COLUMN statement to change the datatype of the column. The syntax to change the datatype of the column is the following.
ALTERTABLE[tbl_name]ALTERCOLUMN[col_name_1]TYPE[DATA_TYPE], ALTERCOLUMN [col_name_2]TYPE[data_type], ALTERCOLUMN[col_name_3]TYPE[data_type] |
In the syntax,
- Tbl_name: Specify the table name that contains the column that you want to change
- Col_name: Specify the column name whose datatype you want to change. The col_name must be specified after the ALTER COLUMN keyword. We can change the data type of multiple columns
- Datatype: Specify the new datatype and length of the column. The datatype must be specified after the TYPE keyword
For demonstration, I have created a table named tblmovies in DemoDatabase. The code to create the table is the following.
createtabletblmovies [ movie_idint, Movie_Titlevarchar[500], Movie_director TEXT, Movie_ProducerTEXT, duraionint, Certificatevarchar[5], rent numeric[10,2] ] |
Now, let us understand the concept using a few examples.
Example 1: SQL query to change the datatype of one column
We want to change the column type of the movie_id column from int4 to int8 data type. Run the following query to change the datatype.
ALTERTABLEtblmoviesALTERCOLUMNmovie_idTYPEBIGINT |
Run the following query to verify the changes:
SELECT table_catalog, table_name, column_name, udt_name, character_maximum_length FROM information_schema.columns WHERE table_name='tblmovies'; |
As you can see, the datatype of the movie_id column has been changed to int8.
Example 2: SQL query to change the datatype of multiple columns
We can change the datatype of multiple columns of a table. In our example, we want to change the column type of the movie_title and movie_director. The new datatype of the movie_title columns is TEXT, and the new datatype of movie_producer is varchar[2000].
ALTERTABLEtblmoviesALTERCOLUMNmovie_titleTYPEtext,ALTERCOLUMNmovie_producerTYPEvarchar[2000]; |
Run the following query to verify the changes:
SELECT table_catalog, table_name, column_name, udt_name, character_maximum_length FROM information_schema.columns WHERE table_name='tblmovies'; |
As you can see, the datatype of the movie_title columns is TEXT, and the datatype of movie_producer is varchar[2000].
Summary
In this article, we learned how we could change the data type of the columns in SQL Server 2019, MySQL Server, and PostgreSQL.
- Author
- Recent Posts
Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on