Table of Contents
In MySQL, multiple columns can be dropped from a table by using the ALTER TABLE statement with the DROP COLUMN clause. This allows the user to specify which columns to drop from the table. For example, if we have a table named “employees” with columns “first_name”, “last_name”, and “salary”, the following command can be used to drop the “last_name” and “salary” columns:
ALTER TABLE employees
DROP COLUMN last_name, salary;
This will remove the specified columns from the table, leaving only the “first_name” column in the table. The use of the DROP COLUMN clause in the ALTER TABLE statement allows for efficient and precise dropping of multiple columns in MySQL.
You can use the following syntax in MySQL to drop multiple columns from a table at once:
ALTER TABLE athletes
DROP team,
DROP assists,
DROP rebounds;
This particular example drops the columns named team, assists and rebounds from the table named athletes.
The following example shows how to use this syntax in practice.
Example: How to Drop Multiple Columns in MySQL
Suppose we have the following table named athletes that contains information about various basketball players:
-- create table
CREATE TABLE athletes (
id INT PRIMARY KEY,
team TEXT NOT NULL,
points INT NOT NULL,
assists INT NOT NULL,
rebounds INT NOT NULL
);
-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 4, 3);
INSERT INTO athletes VALUES (0002, 'Kings', 14, 5, 13);
INSERT INTO athletes VALUES (0003, 'Lakers', 37, 6, 10);
INSERT INTO athletes VALUES (0004, 'Nets', 19, 10, 3);
INSERT INTO athletes VALUES (0005, 'Knicks', 26, 12, 8);
INSERT INTO athletes VALUES (0006, 'Celtics', 15, 1, 2);
-- view all rows in table
SELECT * FROM athletes;
Output:
+----+---------+--------+---------+----------+
| id | team | points | assists | rebounds |
+----+---------+--------+---------+----------+
| 1 | Mavs | 22 | 4 | 3 |
| 2 | Kings | 14 | 5 | 13 |
| 3 | Lakers | 37 | 6 | 10 |
| 4 | Nets | 19 | 10 | 3 |
| 5 | Knicks | 26 | 12 | 8 |
| 6 | Celtics | 15 | 1 | 2 |
+----+---------+--------+---------+----------+ We can use the following syntax to drop the team, assists and rebounds columns from the table:
ALTER TABLE athletes
DROP team,
DROP assists,
DROP rebounds; Output:
+----+--------+
| id | points |
+----+--------+
| 1 | 22 |
| 2 | 14 |
| 3 | 37 |
| 4 | 19 |
| 5 | 26 |
| 6 | 15 |
+----+--------+ Notice that the team, assists and rebounds columns have all been dropped from the table.
The id and points columns are the only columns that remain.
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL: