SQL Modify Column Syntax

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 October 12, 2024.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.