Delete Column from Table

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:

ALTER TABLE Table_Name DROP Column_Name;

The syntax for deleting Multiple Columns from the table is given below:

ALTER TABLE Table_Name DROP Column_Name1, Column_Name2, ......, Column_NameN;

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:

CREATE DATABASE Database_Name;

Suppose you want to create the Vehicles database. For this, you have to write the below CREATE command in Structured Query Language:

CREATE DATABASE Vehicles;

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:

CREATE TABLE table_name
(
column_Name1 data type (size of the column),
column_Name2 data type (size of the column),
column_Name3 data type (size of the column),
...
column_NameN data type (size of the column)
);

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:

CREATE TABLE Cars
(
Car_Number Int,
Model Int,
Cars_Name Varchar (20),
Colour Varchar (20),
Price Int,
Average Int,
) ;

After the table creation, you have to insert the data of cars in the Cars table using the following query in SQL:

INSERT INTO Cars VALUES (1, 2017, Audi, Black, 4800000, 9),
(2, 2018, BMW, Black, 2900000, 12),
(3, 2019, Creta, Blue, 2000000, 14),
(4, 2020, Scorpio, Black, 1900000, 10),
(5, 2018, Mercedes, Grey, 4500000, 10),
(6, 2017, Venue, Yellow, 900000, 15),
(7, 2019, Thar, Red, 1500000, 12),
(8, 2020, Audi, Black, 7000000, 8),
(9, 2019, Creta, Orange, 1500000, 12),
(10, 2020, Verna, Black, 1400000, 12),
(11, 2018, Thar, Red, 1550000, 11),
(12, 2020, MG Hector, Black, 1900000, 11);

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:

SELECT * FROM Cars;

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:

ALTER TABLECars DROP Color;

Step 5: View the Table after Deletion

To check the result of the query executed in the 4^th step, you have to type the following SELECT command in SQL:

SELECT * FROM Cars;

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:

ALTER TABLECars DROP Price, Average;

To check the result of the above query, you have to run the following SQL SELECT query in your system:

SELECT * FROM Cars;

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:

ALTER TABLETable_Name DROP CONSTRAINT Foreign_Constraint_Name;

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz