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, 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.
. 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...;
- Oracle database
ALTER TABLE table_name DROP(column_name1, column_name2...);
- MySQL and PostgreSQL databases.
ALTER TABLE table_name DROP column_name1, DROP column_name2...;
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 tableCREATE TABLE students ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, gender TEXT NOT NULL, standard INTEGER NOT NULL, section TEXT NOT NULL);
Now let’s insert some data to our students table using the below SQL commands:
-- insert some valuesINSERT 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');
Now we can view the complete table with its data using the below command:
SELECT * FROM students;
Output:
So currently we have 5 columns as listed below:
- id
- name
- gender
- standard
- 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;
After executing the above statement, use the below statement to view the table and its structure;
Output:
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;
After executing the above statement, use the below statement to view the table and its structure;
SELECT * FROM students;
Output:
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
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​.