How to Delete a Column in SQL?

How to Delete a Column in SQL?

4 mins read15.7K Views Comment
clickHere
Updated on Jan 22, 2024 12:32 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.

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!!

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

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio

Comments