How to Add Column in SQL
SQL is an important tool for modern data management, and proficiency in it is essential for everyone. This article covers modifying table structures, focusing on adding columns to a MySQL table. Whether you're a student or a professional, this guide will help you improve your SQL skills.
Adjusting table structures without losing data is important for meeting business needs. SQL provides the ALTER TABLE statement to add, delete or modify columns and constraints in a table after creation. This is very useful while introducing new fields to collect extra information from applications and systems built on the database.
Prerequisites for Adding Columns
Before adding a new column, consider these prerequisites:
- Confirm you have privileges to alter the target table in the database.
- Add appropriate default values or constraints if needed for data integrity
- Understand implications for existing queries, indexes, triggers and application code
Now, we will discuss methods to add columns into a table and then the process to INSERT data in that column.
This article will discuss three different methods of adding columns in SQL.
Checkout the Top Online SQL Courses and Certifications
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
Methods for Adding Columns in SQL
Adding a Column using ALTER TABLE Statement in SQL
To add a single column to an existing table in SQL, you can use the below syntax:
ALTER TABLE table_name ADD column_name data_type;
Example: Let you have to add a column 'email' to an employee table, then:
ALTER TABLE employeeADD email VARCHAR(255);
Adding Multiple Columns to a Table using ALTER TABLE Statement in SQL
Now, if you have to add multiple columns to a table, then you just have to ADD clause that many times.
Example: You have to add two columns, email and phone number, to the employee table, then:
ALTER TABLE employeeADD email VARCHAR(255)ADD Phone_Number INT(10);
In the above two sections, you have seen how to add columns to an existing table. Now we will see how to INSERT data in these columns.
Adding Data to a Table Using INSERT Statement in SQL
To add data to the new columns, we will use INSERT INTO and VALUES clauses in SQL.
Let's take an example to get a better understanding of how to add data to a new table.
In the above example, we have added two columns: email and address; now, we will fill one entry in these two columns.
INSERT INTO employee(name, age, email, phone_number)VALUES ('Vikram', '27', 'singh.vikram@shiksha.com', '9876543210');
Note: If you are adding data (or records) to the existing table, you can use the UPDATE clause in SQL.
You may have a basic understanding of how to add new columns to a table. However, if you are still unsure, there is no need to worry. We have prepared a case study to provide a real-life example and help you better understand the concept.
Case Study:
Let there be an 'employee' table having three columns: employee_id, employee_name, and employee_age.
| id | name | age |
|---|---|---|
| 1 | Aarav | 25 |
| 2 | Vivaan | 30 |
| 3 | Aditya | 22 |
| 4 | Arjun | 28 |
| 5 | Aryan | 27 |
| 6 | Vihaan | 32 |
| 7 | Sai | 24 |
| 8 | Kabir | 26 |
| 9 | Ishaan | 29 |
| 10 | Rohan | 31 |
Problem Statement: We need to add additional information about these users, specifically their email addresses and phone numbers. To accomplish this, we will:
- Add a column for email addresses.
- Add multiple columns, one for phone numbers and another for city of residence.
- Insert data into these new columns.
Ans-1: Add a column to e-mail addresses
SQL Command
ALTER TABLE employee ADD email VARCHAR(255);
Output
| id |
name |
age |
|
| 1 |
Aarav |
25 |
None |
| 2 |
Vivaan |
30 |
None |
| 3 |
Aditya |
22 |
None |
| 4 |
Arjun |
28 |
None |
| 5 |
Aryan |
27 |
None |
| 6 |
Vihaan |
32 |
None |
| 7 |
Sai |
24 |
None |
| 8 |
Kabir |
26 |
None |
| 9 |
Ishaan |
29 |
None |
| 10 |
Rohan |
31 |
None |
Ans 2: Add multiple columns, one for phone numbers and another for the city of residence.
SQL Command
ALTER TABLE employee ADD phone_number VARCHAR(20), ADD city TEXT;
Output
| id |
name |
age |
|
phone_number |
city |
| 1 |
Aarav |
25 |
None |
None |
None |
| 2 |
Vivaan |
30 |
None |
None |
None |
| 3 |
Aditya |
22 |
None |
None |
None |
| 4 |
Arjun |
28 |
None |
None |
None |
| 5 |
Aryan |
27 |
None |
None |
None |
| 6 |
Vihaan |
32 |
None |
None |
None |
| 7 |
Sai |
24 |
None |
None |
None |
| 8 |
Kabir |
26 |
None |
None |
None |
| 9 |
Ishaan |
29 |
None |
None |
None |
| 10 |
Rohan |
31 |
None |
None |
None |
Now, for the last and final problem, we will use the UPDATE query as we have to update the record into the existing table. So, in the above table, we will update the first 2 records (i.e., for employee id 1 and 2).
Ans 3: Insert data into newly added columns.
SQL Command
UPDATE employee SET email = 'aarav@gmal.com, phone_number = '9876543210', city = 'Mumbai'UPDATE employee SET email = 'vivaan@gmal.com, phone_number = '9876543211', city = 'Delhi';
Output
| id |
name |
age |
|
phone_number |
city |
| 1 |
Aarav |
25 |
aarav@gmail.com |
9876543210 |
Mumbai |
| 2 |
Vivaan |
30 |
vivaan@gmail.com |
9876543211 |
Delhi |
| 3 |
Aditya |
22 |
None |
None |
None |
| 4 |
Arjun |
28 |
None |
None |
None |
| 5 |
Aryan |
27 |
None |
None |
None |
| 6 |
Vihaan |
32 |
None |
None |
None |
| 7 |
Sai |
24 |
None |
None |
None |
| 8 |
Kabir |
26 |
None |
None |
None |
| 9 |
Ishaan |
29 |
None |
None |
None |
| 10 |
Rohan |
31 |
None |
None |
None |
From the above case study, I hope you understand enough how to add columns to a table and data to the existing column.
Conclusion
The article discussed different methods to add single or multiple columns in a table with the help of a case study. The case study contains three problems,
- Adding one column to the table.
- Adding multiple columns to the table.
- INSERT/UPDATE column to the table.
Hope you will like the article.
Keep Learning!!
Keep Sharing!!
Please Checkout More SQL Blogs
Read Later
FAQs on How to ADD Columns in SQL
How do I add a single column to a table in SQL?
Use the ALTER TABLE statement. For example: ALTER TABLE table_name ADD column_definition;. This is a common approach across various SQL databases like Oracle, SQL Server, SQLite, and DB2.
Can I add multiple columns in one statement?
Yes, in most SQL databases, you can add multiple columns in a single ALTER TABLE statement by listing the columns separated by commas. However, in SQLite, you must execute multiple ALTER TABLE ADD COLUMN statements for each column.
How to set default values when adding a column?
Specify the default value in the column definition. For example: ALTER TABLE customer ADD suburb VARCHAR2(100) DEFAULT 'Central';
Is it possible to add a column with a unique constraint?
Yes, you can add a column and enforce uniqueness. For example: ALTER TABLE users ADD username VARCHAR(50) UNIQUE;. Ensure no duplicated values exist before applying this constraint
How do I check if a column exists before adding it?
Use an IF NOT EXISTS condition. For example: IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SampleTable' AND COLUMN_NAME = 'Email') BEGIN ALTER TABLE SampleTable ADD Email varchar(255) END;
Can I add a column at a specific position in the table?
While it's technically possible, it's not recommended due to the potential impact on existing queries and applications. The default behaviour is to add the new column at the end of the table.
What are some common errors to watch out for when adding columns?
Common issues include syntax errors, data type mismatches, insufficient permissions, and conflicts with existing column names.
How can I rename an existing column?
Use the sp_rename command. For instance, to rename a column: sp_rename 'table_name.old_col_name', 'new_col_name', 'COLUMN';
How do I remove a column from a table?
Use the ALTER TABLE statement with the DROP COLUMN option. For example: ALTER TABLE Employee DROP COLUMN E_Address;
Top Picks & New Arrivals
