Home » How to Delete Column from Table in SQL

How to Delete Column from Table in SQL

by Online Tutorials Library

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:

  1. Create a Database in your system.
  2. Create a Table in the database and Insert the data into the table.
  3. Show the table before column deletion.
  4. Delete a single column from the table.
  5. 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:

Car_Number Model Car_Name Color Price Average
1 2017 Audi Black 4800000 49
2 2018 BMW Black 2900000 50
3 2019 Creta Blue 2000000 45
4 2020 Scorpio Black 1900000 45
5 2018 Mercedes Grey 4500000 50
6 2017 Venue Yellow 900000 35
7 2019 Thar Red 1500000 45
8 2020 Audi Black 7000000 40
9 2019 Creta Orange 1500000 40
10 2020 Verna Black 1400000 35
11 2018 Thar Red 1550000 30
12 2020 MG Hector Black 1900000 25

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:

Car_Number Model Car_Name Price Average
1 2017 Audi 4800000 49
2 2018 BMW 2900000 50
3 2019 Creta 2000000 45
4 2020 Scorpio 1900000 45
5 2018 Mercedes 4500000 50
6 2017 Venue 900000 35
7 2019 Thar 1500000 45
8 2020 Audi 7000000 40
9 2019 Creta 1500000 40
10 2020 Verna 1400000 35
11 2018 Thar 1550000 30
12 2020 MG Hector 1900000 25

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:

Car_Number Model Car_Name Color
1 2017 Audi Black
2 2018 BMW Black
3 2019 Creta Blue
4 2020 Scorpio Black
5 2018 Mercedes Grey
6 2017 Venue Yellow
7 2019 Thar Red
8 2020 Audi Black
9 2019 Creta Orange
10 2020 Verna Black
11 2018 Thar Red
12 2020 MG Hector Black

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:


You may also like