MySQL: How to Drop Multiple Tables at Once


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 Drop Multiple Columns
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 *