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: Show
SQL query to change the column type in SQL Server databaseWe can use ALTER TABLE ALTER COLUMN statement to change the column type of the table. The syntax to change the column type is following:
In the syntax,
For demonstration, I have created a table named tblStudent.
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.
Verify the changes by running following script.
As you can see, the column data type has been changed. Important Notes:
SQL query to change the column type in MySQL ServerWe 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.
In the syntax,
For demonstration, I have created a table named tblactor in DemoDatabase. The code to create the table is the following
Now, let us understand the concept using a few examples. Example 1: SQL query to change the datatype of one columnWe want to change the column type of the address column from varchar(500) to TEXT datatype. Run the following query to change the datatype.
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 columnsWe 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).
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 ServerTo 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.
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 databaseWe 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.
In the syntax,
For demonstration, I have created a table named tblmovies in DemoDatabase. The code to create the table is the following.
Now, let us understand the concept using a few examples. Example 1: SQL query to change the datatype of one columnWe 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.
Run the following query to verify the changes:
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 columnsWe 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).
Run the following query to verify the changes:
As you can see, the datatype of the movie_title columns is TEXT, and the datatype of movie_producer is varchar(2000). SummaryIn this article, we learned how we could change the data type of the columns in SQL Server 2019, MySQL Server, and PostgreSQL.
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 Which command is used to change the structure of the database table?ALTER Command:
ALTER SQL command is a DDL (Data Definition Language) statement. ALTER is used to update the structure of the table in the database (like add, delete, modify the attributes of the tables in the database).
Which SQL command show the structure of table?So desc or describe command shows the structure of table which include name of the column, data-type of column and the nullability which means, that column can contain null values or not.
Can we change structure of table?We can also change its structure i.e. delete, remove or change its column(s) using the ALTER TABLE statement. Syntax: ALTER TABLE <table_name> ADD/DROP <column_name> [datatype]; ALTER TABLE <table> MODIFY <column> <new_definition>; If we want to add a column named Mark in the student table .
What SQL command is used to change the structure of a database object?ALTER is an SQL command used in Relational DBMS and is a Data Definition Language (DDL) statement. ALTER can be used to update the table's structure in the database (like add, delete, drop indexes, columns, and constraints, modify the attributes of the tables in the database).
|