How To Update Column Name In Sql

This article covers the different SQL queries to change the cavalcade type. We are going to acquire how we can change the information type of the columns of the following databases:

1. SQL Server 2019
2. MySQL Server
3. PostgreSQL

SQL query to change the column type in SQL Server database

We can utilise ALTER Table ALTER COLUMN statement to change the cavalcade type of the table. The syntax to change the column blazon is following:

ALTERTABLE[ tbl_name ]ALTERCOLUMN[ col_name_1 ][ DATA_TYPE ]

In the syntax,

• Tbl_name: Specify the table name
• Col_name: Specify the column proper noun whose datatype yous want to change. The col_name must be specified after the ALTER Cavalcade keyword
• Datatype: Specify the new datatype and length of the column

For demonstration, I have created a table named tblStudent.

CREATE TABLE [ dbo ] . [ tblstudent ] ( [ id ] [ INT ] IDENTITY ( 1 , 1 ) NOT NULL , [ student_code ] [ VARCHAR ] ( 20 ) Non Goose egg , [ student_firstname ] [ VARCHAR ] ( 250 ) NOT Cypher , [ student_lastname ] [ VARCHAR ] ( 10 ) Non Cipher , [ accost ] [ VARCHAR ] ( max ) Zero , [ city_code ] [ VARCHAR ] ( 20 ) Not Cipher , [ school_code ] [ VARCHAR ] ( twenty ) Zilch , [ admissiondate ] [ DATETIME ] NULL , CONSTRAINT [ PK_ID ] PRIMARY Cardinal Clustered ( [ id ] ASC ) )

Suppose yous want to alter the data type of [address] from varchar(max) to nvarchar(1500). Run the following query to change the column type.

Modify table tblstudent alter cavalcade address nvarchar ( 1500 )

Verify the changes by running post-obit script.

use StudentDB become select TABLE_SCHEMA , TABLE_NAME , COLUMN_NAME , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA . COLUMNS where table_name = 'tblStudent'

As you can run into, the column data type has been inverse.

Important Notes:

1. When nosotros 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, information technology returns the warning and terminate the statement
2. 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
3. Unlike MySQL, the changing datatype of multiple columns is not allowed
4. You cannot add together
   1. NOT NULL constraint if the column contains the NULL values
   2. UNIQUE constraint if the column has duplicate values

SQL query to change the column type in MySQL Server

We can use Modify Tabular array MODIFY COLUMN statement to change the datatype of the cavalcade. The syntax to alter the datatype of the column is following.

ALTER TABLE [ tbl_name ] MODIFY Cavalcade [ col_name_1 ] [ DATA_TYPE ] , Change [ col_name_2 ] [ data_type ] , Change [ col_name_3 ] [ data_type ]

In the syntax,

• Tbl_name: Specify the table proper noun that contains the column that you want to change
• Col_name: Specify the cavalcade proper name whose datatype you want to change. The col_name must be specified later the Alter Cavalcade keyword. Nosotros can change the data type of multiple columns. When nosotros change the datatype of multiple columns, each cavalcade must exist separated with a comma (,)
• Datatype: Specify the new datatype and length of the cavalcade. The datatype must be specified after the cavalcade name

For demonstration, I have created a table named tblactor in DemoDatabase. The code to create the tabular array is the following

create table tblactor ( actor_id int , first_name varchar ( 500 ) , first_name varchar ( 500 ) , accost varchar ( 500 ) , CityID int , lastupdate datetime )

Now, let usa empathize the concept using a few examples.

Example 1: SQL query to change the datatype of 1 cavalcade

We want to change the column type of the address cavalcade from varchar(500) to TEXT datatype. Run the post-obit query to change the datatype.

mysql> Modify Table tblActor MODIFY accost TEXT

Run the following query to verify the changes:

As you tin can see, the datatype of the accost column has been changed to TEXT .

Instance 2: SQL query to alter the datatype of multiple columns

We can alter the datatype of multiple columns of a table. In our case, nosotros want to alter the cavalcade type of the first_name and last_name. The new datatype of the columns is varchar(200).

mysql> ALTER Table tblActor MODIFY first_name TINYTEXT, alter last_name TINYTEXT;

Run the following query to verify the changes:

As y'all can run across, the datatype of the first_name and last_name cavalcade has been changed to TINYTEXT.

Instance iii: Rename the column in MySQL Server

To rename the columns, we must use Change TABLE Alter COLUMN statement. Suppose y'all want to rename the cavalcade CityID to CityCode, you must execute the following query.

mysql> Modify Tabular array tblActor CHANGE Cavalcade CityID CityCode int

Run the describe command to view the changes in table structure.

As yous can meet, the column name has been changed.

SQL query to change the column blazon in PostgreSQL database

Nosotros can use Modify Tabular array Modify COLUMN statement to change the datatype of the column. The syntax to change the datatype of the cavalcade is the post-obit.

Modify Tabular array [ tbl_name ] ALTER COLUMN [ col_name_1 ] TYPE [ DATA_TYPE ] , Change COLUMN [ col_name_2 ] Blazon [ data_type ] , Modify Cavalcade [ col_name_3 ] TYPE [ data_type ]

In the syntax,

• Tbl_name: Specify the table proper name that contains the column that you lot want to alter
• Col_name: Specify the column name whose datatype you want to change. The col_name must be specified later on the Modify Column keyword. We can modify the data type of multiple columns
• Datatype: Specify the new datatype and length of the column. The datatype must exist 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.

create table tblmovies ( movie_id int , Movie_Title varchar ( 500 ) , Movie_director TEXT , Movie_Producer TEXT , duraion int , Certificate varchar ( 5 ) , rent numeric ( x , 2 ) )

At present, let us sympathise the concept using a few examples.

Case 1: SQL query to change the datatype of one column

Nosotros want to change the column blazon of the movie_id column from int4 to int8 information type. Run the post-obit query to change the datatype.

ALTER Table tblmovies ALTER Column movie_id TYPE BIGINT

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' ;

Equally yous tin 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

Nosotros can change the datatype of multiple columns of a table. In our example, we desire 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).

Modify TABLE tblmovies Alter Cavalcade movie_title Type text , ALTER Column movie_producer Type varchar ( 2000 ) ;

Run the post-obit 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, nosotros learned how we could change the data type of the columns in SQL Server 2019, MySQL Server, and PostgreSQL.

• Author
• Contempo Posts

Nisarg Upadhyay is a SQL Server Database Ambassador and Microsoft certified professional who has more than eight years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database blueprint, operation tuning, fill-in and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

How To Update Column Name In Sql,

Source: https://www.sqlshack.com/sql-queries-to-change-the-column-type/

Posted by: lampmanpridn1945.blogspot.com

Share this post