How to Delete a Column in SQL?

How to Delete a Column in SQL?

5 mins read15.7K Views Comment
Updated on Aug 22, 2024 08:49 IST

Sometimes, in the dataset, you have some columns that are not of your use, but when you run a query, it will also get executed, which leads to an increase in your execution time. In SQL, you can use ALTER TABLE and DROP commands to delete a column in the dataset. This article will briefly discuss how to delete a column in SQL.

how to delete column in sql

Deleting a column in a relational database using SQL involves a crucial aspect of database management and maintenance. As databases evolve and data needs change, removing unnecessary or redundant columns arises. Whether you’re streamlining your data structure, complying with data privacy regulations, or optimizing database performance, understanding the process of deleting a column is essential. This blog will explore the steps and considerations involved in deleting a column from a table using SQL, ensuring you can efficiently manage your database schema while preserving data integrity.

. To delete a column in SQL, we use the ALTER TABLE command to change the table’s structure with the DROP keyword to delete the specific column.

Syntax:

  • MS SQL Server

 
ALTER TABLE table_name DROP column_name1, column_name2...;
Copy code
  • Oracle database

 
ALTER TABLE table_name DROP(column_name1, column_name2...);
Copy code
  • MySQL and PostgreSQL databases.

 
ALTER TABLE table_name DROP column_name1, DROP column_name2...;
Copy code

Must Read: Introduction to SQL

Must Read: SQL ACID Properties

In the above syntaxes:

  • First, we specify the table name after the ALTER TABLE command from which the column is to be removed.
  • Then we specify the column name that is to be removed after the DROP keyword.

Checkout the Top Online SQL Courses and Certifications

Note: For the purpose of demonstration, we will be working with a MySQL database in this article.

Now that we know the syntax to delete columns from a database, let’s set up one table and use the above syntax on it to delete columns in that table.

To create a simple table with 5 columns, use the following SQL statement:


 
-- create a table
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
gender TEXT NOT NULL,
standard INTEGER NOT NULL,
section TEXT NOT NULL
);
Copy code

Now let’s insert some data to our students table using the below SQL commands:


 
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M',5,'A');
INSERT INTO students VALUES (2, 'Joanna', 'F',7,'B');
INSERT INTO students VALUES (3, 'Mike', 'F',8,'C');
INSERT INTO students VALUES (4, 'Harvey', 'F',11,'D');
INSERT INTO students VALUES (5, 'Donna', 'F',12,'A');
Copy code

Now we can view the complete table with its data using the below command:


 
SELECT * FROM students;
Copy code

Output:

2022_08_image-66.jpg

So currently we have 5 columns as listed below:

  1. id
  2. name
  3. gender
  4. standard
  5. section

Now let’s jump into some examples where we will be deleting columns from the students table that we just created above.

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

Example 1: Deleting a Single column.

In this example, we will delete the section column from the students table. For that, we can use the below SQL command:


 
ALTER TABLE students DROP section;
Copy code

After executing the above statement, use the below statement to view the table and its structure;

Output:

2022_08_image-67.jpg

Must Read: Subqueries in SQL

Must Read: SQL CREATE TABLE

Example 2: Deleting multiple columns.

In this example, we will delete the gender and standard columns from the students table. For this, we can use the below SQL command:


 
ALTER TABLE students DROP gender, standard;
Copy code

After executing the above statement, use the below statement to view the table and its structure;


 
SELECT * FROM students;
Copy code

Output:

2022_08_image-68.jpg

Must Read: SQL LIMITS

Must Check: SQL Online Course and Certifications

Conclusion:

In this article, we have managed to learn the process of deleting single or multiple columns from a table. We have also established the use of the ALTER command while changing the structure of a table. Then, only we can use the DROP keyword actually to delete a column(s).
Hope you will like the article.
Keep Learning!!
Keep Sharing!!

Please Checkout More SQL Blogs

How to Find Nth Highest Salary in SQL
How to Find Nth Highest Salary in SQL
Finding out the N’th highest salary from a table is one of the most frequently asked SQL interview questions. In this article, we will discuss four different approaches to find...read more

Order of Execution in SQL
Order of Execution in SQL
An SQL query comprises of various clauses like SELECT, FROM, WHERE, GROUPBY, HAVING, and ORDERBY clauses. Each clause has a specific role in the query. The correct order of execution...read more

How to Find Second Highest Salary in SQL
How to Find Second Highest Salary in SQL
It's crucial to master SQL queries for managing and analyzing databases. This article focuses on finding the second-highest salary in SQL, which is a common yet important task in database...read more

Using Partition By SQL Clause
Using Partition By SQL Clause
The Partition By SQL clause is a subclause of OVER clause that is used in every invocation of window functions such as MAX(), RANK() and AVG(). In SQL, the “PARTITION...read more

Top 30 SQL Query Interview Questions
Top 30 SQL Query Interview Questions
Structured Query Language or most commonly known as SQL is used on a daily basis to handle, manipulate and analyze relational databases.

SQL ALTER TABLE : ADD, DROP, MODIFY, RENAME
SQL ALTER TABLE : ADD, DROP, MODIFY, RENAME
ALTER TABLE in SQL is used to change the structure of the existing table. In this article, we will briefly discuss how to add, modify, drop, rename columns, constraints, and...read more

How to Delete a Column in SQL?
How to Delete a Column in SQL?
Sometimes in the dataset, you have some columns that are not of your use but when you run a query it will also get executed that leads to you increase...read more

How to Use TRUNCATE Command in SQL?
How to Use TRUNCATE Command in SQL?
SQL TRUNCATE TABLE command is used to remove the record from the table. Confused, the SQL DELETE statement also removes the record from the table. So, why use another command...read more

How to use DROP command in SQL?
How to use DROP command in SQL?
In SQL, the DROP command is used to delete a database, table, index, or view. To use the DROP command, you must be very careful because it permanently deletes the...read more

How to Create, Update, Insert and Delete SQL Views?
How to Create, Update, Insert and Delete SQL Views?
This article will give you a detailed insight on SQL views. You will learn the method to create, update, insert, drop and delete SQL views. This article will give you...read more

All About Natural Joins in SQL
All About Natural Joins in SQL
Natural join is an inner join that automatically joins two or more tables with the same name and data type on all columns. But both inner join and natural join...read more

100+ SQL Interview Questions and Answers for 2023
100+ SQL Interview Questions and Answers for 2023
Are you wondering what SQL interview questions you will be asked? If you are preparing for an SQL interview, this article will provide you with 100+ most commonly asked SQL...read more

Difference Between SQL and PLSQL
Difference Between SQL and PLSQL
In this article, we will learn what SQL is, What PLSQL is, their types, features, applications, and their differences.

75 Most Popular MySQL Commands
75 Most Popular MySQL Commands
This article covers the most popular MySQL commands with examples to help you work more efficiently with MySQL databases. This article covers the most popular MySQL commands with examples to...read more

FAQs on How to DELETE Columns in SQL

What is the basic command to delete a column in SQL?

The standard command used to delete a column in SQL is ALTER TABLE <TableName> DROP COLUMN <ColumnName>;. This command removes the specified column from the table, including all associated data​.

Can I delete multiple columns at once in SQL?

Yes, you can delete multiple columns in a single command by listing them as comma-separated values in the DROP COLUMN clause: ALTER TABLE <TableName> DROP COLUMN <ColumnName1>, <ColumnName2>, ...;.

Are there any prerequisites or considerations before deleting a column?

Before deleting a column, ensure you have access to the database, necessary permissions, and a basic understanding of SQL syntax. It's important to remember that this action is irreversible and will permanently delete the data in the column. Always backup your data before proceeding with the deletion.

What are the common errors and how can I troubleshoot them when deleting columns?

Common issues include syntax errors and lack of permissions. Double-check your query for any syntax mistakes and ensure you have the required privileges to modify the table. Having a backup of your database is also recommended as a precautionary measure​.

What should I do if a column is involved in a constraint?

If the column you wish to delete is part of a constraint (like a CHECK constraint), you must first remove the constraint using the ALTER TABLE <TableName> DROP CONSTRAINT <ConstraintName>; command. After removing the constraint, you can proceed to delete the column.

Can the DROP COLUMN command be used for all SQL databases?

The ALTER TABLE ... DROP COLUMN ... syntax is widely supported across various SQL databases like SQL Server, MySQL, PostgreSQL, and SQLite. However, always check the specific documentation for your database system as there might be slight variations​

What is the difference between DROP COLUMN and DROP TABLE?

DROP COLUMN is used to remove a specific column from a table, whereas DROP TABLE is used to remove an entire table from the database. DROP COLUMN only affects the specified column, whereas DROP TABLE will remove all columns and data within the table.

Are there any alternative methods to remove a column from a table?

An alternative method involves creating a new table with the desired columns, copying data from the original table, and then renaming the new table to replace the original. This method is more complex and typically used when you want to preserve certain aspects of the data or structure​.

About the Author