SQL > SQL ALTER TABLE > Add Column Syntax

To add a column to a table using SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the ADD command to tell the RDBMS that we want to add a column.

Syntax

For MySQL, Oracle, and SQL Server, the syntax for ALTER TABLE Add Column is,

ALTER TABLE "table_name"
ADD "column_name" "Data Type";

For Google BigQuery, the syntax for ALTER TABLE Add Column is,

ALTER TABLE "table_name"
ADD COLUMN "column_name" "Data Type" [, ...];

For SparkSQL and Hive SQL (HiveQL), the syntax for ALTER TABLE Add Column is,

ALTER TABLE "table_name"
ADD COLUMNS "column_name" "Data Type";

Examples

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 

Example 1: Add one column to a table

Our goal is to add a column called "Gender". To do this, we key in:

MySQL:

ALTER TABLE Customer ADD Gender char(1);

Oracle:

ALTER TABLE Customer ADD Gender char(1);

SQL Server:

ALTER TABLE Customer ADD Gender char(1);

Google BigQuery:

ALTER TABLE Customer ADD COLUMN Gender char(1);

SparkSQL:

ALTER TABLE Customer ADD COLUMNS Gender char(1);

HiveQL:

ALTER TABLE Customer ADD COLUMNS Gender char(1);

The resulting table structure is:

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 
 Gender  char(1) 

Note that the new column Gender becomes the last column in the Customer table.

Example 2: Add multiple columns to a table

It is also possible to add multiple columns. To do so, start with a parenthesis, then add each column name and its data type separated by comma, in the order that you want the columns to appear.

For example, if we want to add a column called "Email" and another column called "Telephone", we will type the following:

MySQL:

ALTER TABLE Customer ADD (Email char(30), Telephone char(20) );

Oracle:

ALTER TABLE Customer ADD (Email char(30), Telephone char(20) );

SQL Server:

ALTER TABLE Customer ADD (Email char(30), Telephone char(20) );

Google BigQuery:

ALTER TABLE Customer ADD COLUMN Email char(30), ADD COLUMN Telephone char(20);

SparkSQL:

ALTER TABLE Customer ADD COLUMNS (Email char(30), Telephone char(20) );

HiveQL:

ALTER TABLE Customer ADD COLUMNS (Email char(30), Telephone char(20) );

The table now becomes:

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 
 Gender  char(1) 
 Email  char(30) 
 Telephone  char(20) 

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.

Next: SQL CHANGE COLUMN

This page was last updated on October 19, 2023.




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