Table of Contents
Yes, multiple tables can be dropped at once in MySQL by using the DROP TABLE statement. This statement allows for multiple table names to be specified, separated by commas, in order to drop multiple tables in one command. This is useful when a user needs to delete several tables at once, saving time and simplifying the process. It is important to note that this action cannot be undone, so it should be used with caution.
You can use the following syntax in MySQL to drop multiple tables from a database at once:
DROP TABLE IF EXISTS team, assists, steals;
This particular example drops the tables named team, assists and steals from the database currently being used.
Note: We used IF EXISTS before the table names so that we don’t receive an error if we try to reference a table name that doesn’t exist in the database.
The following example shows how to use this syntax in practice.
Example: How to Drop Multiple Tables in MySQL
Suppose we create four tables named team, points, assists and steals to add to a database that we’re currently using:
-- create tables
CREATE TABLE team (
id INT PRIMARY KEY,
team TEXT NOT NULL
);
CREATE TABLE points (
id INT PRIMARY KEY,
points INT NOT NULL
);
CREATE TABLE assists (
id INT PRIMARY KEY,
assists INT NOT NULL
);
CREATE TABLE steals (
id INT PRIMARY KEY,
steals INT NOT NULL
);
-- display all tables in database
SHOW TABLES;
Output:
+----------------------------------+
| Tables_in_sandbox_db |
+----------------------------------+
| assists |
| points |
| steals |
| team |
+----------------------------------+ Now suppose that we would like to drop the tables named team, assists and steals from the database all at once.
We can use the following syntax to do so:
-- drop three tables at once
DROP TABLE IF EXISTS team, assists, steals;
-- display all tables in database
SHOW TABLES;
Output:
+----------------------------------+
| Tables_in_sandbox_db |
+----------------------------------+
| points |
+----------------------------------+ Notice that the team, assists and steals tables have all been dropped from the database.
The points table is the only one that remains.
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL:
MySQL: How to Delete Rows from Table Based on id
MySQL: How to Delete Duplicate Rows But Keep Latest