SQL > SQL ALTER TABLE > Modify Column Syntax

Sometimes we need to change the data type of a column. To do this, we use the ALTER TABLE Modify Column command. For Oracle and MySQL, the SQL syntax for ALTER TABLE Modify Column is,

ALTER TABLE "table_name"
MODIFY "column_name" "New Data Type";

For SQL Server, the syntax is,

ALTER TABLE "table_name"
ALTER COLUMN "column_name" "New Data Type";

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 

Our goal is to alter the data type of the "Address" column to char(100). To do this, we key in:

MySQL:

ALTER TABLE Customer MODIFY Address char(100);

Oracle:

ALTER TABLE Customer MODIFY Address char(100);

SQL Server:

ALTER TABLE Customer ALTER COLUMN Address char(100);

Resulting table structure:

Table Customer

 Column Name  Data Type 
 First_Name  char(50) 
 Last_Name  char(50) 
 Address  char(100) 
 City  char(50) 
 Country  char(25) 
 Birth_Date  datetime 

To change the data type of a column in SparkSQL or Hive SQL, we would use the ALTER TABLE Change Column command.

Next: SQL RENAME COLUMN

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact