DROP multiple columns in MySQL with single ALTER statement

This article will see how to DROP multiple columns from a MySQL table with a single ALTER statement. Multiple columns can be deleted from the MySQL table using a single ALTER statement by separating the DROP keyword and column name list with a comma.

SYNTAX:-

ALTER TABLE table_name DROP column1, DROP column2, DROP column3........;

EXAMPLE:-

Let us get started by preparing the sample data. We will be creating a table, sale_details, followed by inserting rows into it. Finally, we will be deleting three columns from the sale_details table in a single query.

#create the table sale_details
CREATE TABLE sale_details (
    id INT ,
	sale_person_id VARCHAR(255) ,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255)
);
#insert data into table sale_details
INSERT INTO sale_details (id,sale_person_id,sale_person_name,no_products_sold,sales_department) 
 VALUES(1,"sd1","Henry",2000,"Kitchen Essentials"),
 (2,"sd1","Henry",5000,"Apparels"),
 (3,"sd1","Henry",40,"Medicines"),
 (4,"sd2","Richa",3000,"Kitchen Essentials"),
 (5,"sd2","Richa",500,"Apparels"),
 (6,"sd2","Richa",50,"Medicines"),
 (7,"sd3","Ved",100,"Kitchen Essentials"),
 (8,"sd3","Ved",150,"Apparels"),
 (9,"sd3","Ved",1000,"Medicines");

Action Output:-

To view the snapshot of the table sale_details, we will execute:

SELECT * FROM sale_details;

Output:-

We will now be deleting columns sale_person_id, no_products_sold, and sales_department from table sale_details. Observe the below query for the solution.

ALTER TABLE sale_details DROP sale_person_name,DROP no_products_sold, DROP sales_department;

Action Output:-

To verify if the query dropped the columns along with data from table sale_details execute:

SELECT * FROM sale_details;

Output:-

The output in image_4 shows that multiple columns got deleted along with data.

READ MORE:

• Find all tables with specific column names in MySQL
• MySQL add primary key multiple columns
• Mysql update set multiple columns
• Mysql update column with value from another table
• Select all columns except one of a MySQL table
• MySQL ADD COLUMN IF NOT EXISTS
• MYSQL: change column order
• MySQL: change column type to VARCHAR without losing data
• MySQL get column names

We hope this article helped you delete multiple columns from a MySQL table in a single query. Good Luck!!!