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: 1 ALTER TABLE [tbl_name] ALTER COLUMN [col_name_1] [DATA_TYPE] In the syntax,
For demonstration, I have created a table named tblStudent. 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE [dbo].[tblstudent] ( [id] [INT] IDENTITY(1, 1) NOT NULL, [student_code] [VARCHAR](20) NOT NULL, [student_firstname] [VARCHAR](250) NOT NULL, [student_lastname] [VARCHAR](10) NOT NULL, [address] [VARCHAR](max) NULL, [city_code] [VARCHAR](20) NOT NULL, [school_code] [VARCHAR](20) NULL, [admissiondate] [DATETIME] NULL, CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ( [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. 1 Alter table tblstudent alter column address nvarchar(1500) Verify the changes by running following script. 1 2 3 4 use StudentDB 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:
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. 1 2 3 ALTER TABLE [tbl_name] MODIFY COLUMN [col_name_1] [DATA_TYPE], MODIFY [col_name_2] [data_type], MODIFY [col_name_3] [data_type] In the syntax,
For demonstration, I have created a table named tblactor in DemoDatabase. The code to create the table is the following 1 2 3 4 5 6 7 8 9 create table tblactor ( actor_id int, first_name varchar(500), first_name varchar(500), address varchar(500), CityID int, lastupdate datetime ) 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. 1 mysql> ALTER TABLE tblActor MODIFY address TEXT Run the following query to verify the changes: 1 mysql> describe tblactor 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). 1 mysql> ALTER TABLE tblActor MODIFY first_name TINYTEXT, modify last_name TINYTEXT; Run the following query to verify the changes: 1 mysql> describe tblActor 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. 1 mysql> ALTER TABLE tblActor CHANGE COLUMN CityID CityCode int 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. 1 2 3 ALTER TABLE [tbl_name] ALTER COLUMN [col_name_1] TYPE [DATA_TYPE], ALTER COLUMN [col_name_2] TYPE [data_type], ALTER COLUMN [col_name_3] TYPE [data_type] In the syntax,
For demonstration, I have created a table named tblmovies in DemoDatabase. The code to create the table is the following. 1 2 3 4 5 6 7 8 9 10 create table tblmovies ( movie_id int, Movie_Title varchar(500), Movie_director TEXT, Movie_Producer TEXT, duraion int, Certificate varchar(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 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. 1 ALTER TABLE tblmovies ALTER COLUMN movie_id TYPE BIGINT Run the following query to verify the changes: 1 2 3 4 5 6 7 8 9 10 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 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). 1 ALTER TABLE tblmovies ALTER COLUMN movie_title TYPE text, ALTER COLUMN movie_producer TYPE varchar(2000); Run the following query to verify the changes: 1 2 3 4 5 6 7 8 9 10 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). SummaryIn this article, we learned how we could change the data type of the columns in SQL Server 2019, MySQL Server, and PostgreSQL. How do I change all the values in one column in SQL?To set all values in a single column MySQL query, you can use UPDATE command. The syntax is as follows. update yourTableName set yourColumnName =yourValue; To understand the above syntax, let us create a table.
How do I change all column values?Here is the syntax for the statement used to change all values of a table column to a new value:. UPDATE table_name SET column_name = value;. UPDATE table_name SET column_name1 = value_1, column_name2 = value_2;. UPDATE users SET default_password = 'Str0ngp@ssw0rd';. How do you update multiple values in a column in SQL?We can update multiple columns in SQL using the UPDATE command. The UPDATE statement is followed by a SET statement, which specifies the column(s) where the update is required. Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ...
How to replace all column values in MySQL?To replace, use the REPLACE() MySQL function. Since you need to update the table for this, use the UPDATE() function with the SET clause.
|