How to Drop Multiple Columns in MySQL (With Example)


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:

MySQL: How to Use DELETE with INNER JOIN
MySQL: How to Delete Rows from Table Based on id
MySQL: How to Delete Duplicate Rows But Keep Latest

Leave a Reply

Your email address will not be published. Required fields are marked *