How to Delete Column from Table in SQL
This article describes how to delete one or more columns from the table in Structured Query Language.
The ALTER command in SQL deletes the single and multiple columns from the SQL table. It allows the database users to modify the structure of the created table in the database.
The syntax for deleting a Single Column from the table is given below:
The syntax for deleting Multiple Columns from the table is given below:
We have to use the DROP keyword in the ALTER command for deleting one or more columns from the table.
If you want to delete the column from the table, you have to follow the following steps one by one in the given order:
- Create a Database in your system.
- Create a Table in the database and Insert the data into the table.
- Show the table before column deletion.
- Delete a single column from the table.
- Show the table after deletion.
Now, we explain the above steps with an example:
Step 1: Create a Database
In the Structured Query Language, creating a database is the first step for storing the structured tables in the database.
Use the below SQL syntax to create a database:
Suppose you want to create the Vehicles database. For this, you have to write the below CREATE command in Structured Query Language:
Step 2: Create a Table and Insert the data
Now, you have to use the following syntax for creating the table in the SQL database:
Suppose you want to create the Cars table in the Vehicles database. For this, you have to write the following CREATE table query in your SQL application:
After the table creation, you have to insert the data of cars in the Cars table using the following query in SQL:
Step 3: View the Inserted Data
After table creation and data insertion, you can view the inserted data of the Cars table by typing the following query in your SQL application:
Step 4: Delete Single Column from table
If you want to delete the Color column from the above Cars table, then you have to execute the following ALTER query in your SQL database:
Step 5: View the Table after Deletion
To check the result of the query executed in the 4th step, you have to type the following SELECT command in SQL:
As we can see, one column has been successfully deleted / removed from the Cars table.
Delete Multiple Columns from the table
If you want to delete the multiple columns from the above Cars table, then you execute the following ALTER query with the DROP keyword in SQL:
To check the result of the above query, you have to run the following SQL SELECT query in your system:
As we can see, the Price and Average column have been successfully deleted from the Cars table.
Delete Foreign Key Column from table
We can easily delete the foreign key column from the table using the DROP Constraint keyword in the ALTER Command.
The Syntax for deleting the foreign key column is given below: