SQL Delete Table

Table of Contents

Introduction

SQL provides the DROP TABLE command to delete a table.

This tutorial will show you how to delete a table and how to clear all data without removing the table itself.
Don’t worry, each section is full of examples and everything will be explained clearly.

Preliminary operations

First of all, for this tutorial I assume that you have MySQL installed on your computer.
If not, you can download it and follow the instructions to install it.

You probably already know this, but I just want to remember that before deleting a table you need to create a database and select it, otherwise you may run into some errors.
Here are the commands for these two operations:

CREATE DATABASE devinsimplewords;
USE devinsimplewords;

Last but not least, if you want to delete a table you should have one.
If you have an empty database, don’t worry this tutorial on how to create a new table will help you!

How to delete a table in SQL

To delete a table we use the DROP TABLE command which has the following syntax:

DROP TABLE table_name;

If you followed my previous tutorial on how to create a new table, you will have the Users table in your database.

Column NameColumn Type
idInteger
nameString
surnameString
cityString
Users table description

If we wanted to delete it, just use this command:

DROP TABLE Users;

How to delete a table if it does not exist in SQL

In the previous section we saw how to delete a table, in particular we deleted the Users table.
But what happens if we try to repeat the same command?
SQL does not allow us to delete a table that does not exist, so an error is raised.

However in SQL it is possible to slightly modify the DROP TABLE command to work around this problem.

DROP TABLE if exists Users;

By adding if not exists in fact we can run the same command several times and the table will be deleted only the first time.
Mistakes are gone too!

How to clear data without removing table

In SQL, you can also clean up all data without deleting the table. This is done using the TRUNCATE TABLE command.

The syntax of this command is very simple:

TRUNCATE TABLE table_name;

If we want to remove all the data from the Users table, just use this command.

TRUNCATE TABLE Users;

Conclusion

Here we are at the end of this tutorial, as always I hope it was useful to you and that now you know how to best manage each scenario for deleting a table using SQL.
If you are in trouble, do not hesitate to write me in the comments.

Leave a Comment

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