|
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 MODIFY COLUMN changes a column's data type in MySQL and Oracle. SQL Server uses ALTER COLUMN instead. This command does not rename the column — use RENAME COLUMN for that.
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.
Frequently Asked Questions
- Q: What is the difference between MODIFY COLUMN and ALTER COLUMN?
- A: MODIFY COLUMN is used in MySQL and Oracle to change a column's data type. SQL Server uses ALTER COLUMN instead. Both achieve the same result — changing the column's data type definition.
- Q: Can I use MODIFY COLUMN to rename a column?
- A: No. MODIFY COLUMN only changes the data type. To rename a column, use ALTER TABLE RENAME COLUMN (Oracle) or ALTER TABLE CHANGE (MySQL/SparkSQL).
- Q: What happens to existing data when I change a column's data type?
- A: The database attempts to convert existing data to the new type. If conversion is not possible (e.g., converting text to integer when the column contains non-numeric values), the operation will fail.
- Q: How do I change a column's data type in SparkSQL or HiveQL?
- A: In SparkSQL and HiveQL, use ALTER TABLE CHANGE COLUMN instead:
ALTER TABLE Customer CHANGE Address Address char(100);
Next: SQL RENAME COLUMN
This page was last updated on March 19, 2026.
Copyright © 2026 1keydata.com All Rights Reserved
Privacy Policy About Contact |