SQL Change Column Syntax

In SparkSQL and HiveQL, the ALTER TABLE Change Column serves two purposes: It can be used to change the name of a column, and it also be used to change the data type of the column. You can also do both at the same time.

Change Column is not used in traditional RDBMS such as MySQL, Oracle, and SQL Server. In those environments, you can change the name of a column using the ALTER TABLE RENAME COLUMN command, and you can change the data type of a column using the ALTER TABLE MODIFY COLUMN comnmand.

The syntax for ALTER TABLE Change Column is,

ALTER TABLE "table_name"
CHANGE "column_name" "new 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

Our goal is to change the Country column to Region, and give it a data type of char(20). To do this, we key in:

ALTER TABLE Customer CHANGE Country Region char(20);

Resulting table structure:

Table Customer

To only change the data type and not the column name, we would simply repeat the column name in our command. For example, let's say now we want to change the data type of Region to char(35), we would type in,

ALTER TABLE Customer CHANGE Region Region char(35);

Please note that Spark only has a string data type. Specifying a data type of char() will work in SparkSQL, but the new column will have a data type of string.