SQL TRUNCATE TABLE and DROP TABLE (tutorial)

In this episode of the SQL tutorial series you’ll learn two simple but important commands that you’ll use frequently when working in SQL: TRUNCATE TABLE and DROP TABLE.

• TRUNCATE TABLE is to delete all the data from an SQL table.
• DROP TABLE is to delete the table itself.

Let’s see how they work in practice!

Note: This is going to be a hands-on tutorial, so I encourage you to do the coding part with me.
Note 2: If you are new here, start with these SQL articles first:

1. How to install Python, SQL, R and Bash (for non-devs)
2. SQL for Data Analysis – Tutorial for Beginners – ep1
3. How to install SQL Workbench for postgreSQL

SQL TRUNCATE TABLE

Start with the TRUNCATE TABLE command!

You’ll use TRUNCATE TABLE when you want to empty an SQL table… Or in other words: when you want to delete all the data from it but you want to keep the table itself.

Why would you want to keep a table without the data in it? Because that way, you don’t have to CREATE the structure of it again: you don’t have to specify the column names and the data types.

In data science, the SQL TRUNCATE command becomes handy, for instance, when you find a bug in your automated data loading scripts that populates your SQL tables. E.g. a typical bug like that is when a daily data load accidentally happens twice. The easiest way to fix that issue is to empty the table and reload the correct data. (And of course, don’t forget to fix the automated script, too. ;-))

Or another common use case is when you work with million-line tables with fields that have to be updated every day. Instead of updating each row one by one, sometimes it’s more efficient to empty the table and load the updated data.

The syntax is very easy, by the way. It goes:

TRUNCATE TABLE table_name;

TRUNCATE TABLE is the SQL command itself — and table_name should be the name of your already existing table that you want to empty. (And of course, don’t forget the semicolon ; at the end.)

Here’s a concrete example.

If you have three tables:

• users
• activity
• payments

…you can delete all data from the users table using this command:

TRUNCATE TABLE users;

Note: Just as I said — easy as pie. Really there is nothing to mess up here. However, an issue I’ve seen sometimes is that people forget to add the TABLE keyword and they incorrectly go with: TRUNCATE table_name; which of course will return a syntax error. So watch out for that!

Check the results!

Okay, I emptied the table.
If I run SELECT * FROM users; now, I’ll get a table with zero records — but the column names will still be there: 

Perfection!

Don’t forget to commit!

I prefer to use SQL managers to write SQL queries, like SQL Workbench or pgadmin4. And I recommend you do the same.

However keep in mind that with these tools, when you make changes to your table (e.g. when you empty it with the SQL TRUNCATE command) you have to COMMIT your changes! “Commiting” basically means that you’ll publish your changes to your data server. But we have already discussed this in detail in the CREATE TABLE article.

Note: Don’t worry, “publishing” in this case means that you make your changes visible to other users on your server and, of course, it does not mean that it will be available to everyone on the internet.

So to actually make your TRUNCATE TABLE command go through, run this command, too:

COMMIT;

Nice, your table is empty, you can start over importing data to it.

Note: If you turned auto-commit on or if you are in the command line and not in an SQL query tool, then you can skip this commit step.

SQL DROP TABLE

Our second command is DROP TABLE.

As opposed to TRUNCATE TABLE, DROP TABLE doesn’t just empty the SQL table but instead deletes it. It means that you’ll delete its structure, the previously defined column names, the data types you set — and of course the data in it.

So it’s a more radical move.

Regardless, sometimes it’s needed. In data science, you’ll use it when you make changes to your whole database structure and don’t need some of your SQL tables anymore… Or when you accidentally create a table that you don’t want to use. (It happens, folks.) Also, sometimes it’s easier and faster to DROP a table when you mistyped something in it (table name, column name, data type) than fixing it with ALTER TABLE.

Here’s the syntax of deleting an SQL table:

DROP TABLE table_name;

DROP TABLE is the command itself and table_name is the name of the table that you want to delete.

This one’s easy, as well.

But just in case, here’s an example, too.

Let’s say that you have these three tables again:

• users
• activity
• payments

…and you want to delete the activity table. Just type this:

DROP TABLE activity;

Then don’t forget to COMMIT your changes here, either!

Once it’s done and you try to query your activity table, you’ll get an error message that says that this table doesn’t exist:

Just as we wanted!

Great!

Warning before using SQL TRUNCATE TABLE and DROP TABLE

Now that you know how to empty or delete a table in SQL, here’s a friendly warning.

Whether you use the SQL TRUNCATE TABLE or DROP TABLE command: be very careful! Sure, if you use SQL Workbench (or pgadmin4), you’ll have to commit your changes before they become irreversible… But apart from that, there is no undo button here.

Deleting or emptying a table can lead to significant – and painful – data losses, so think twice before you actually run them. Especially in real life projects. And especially when you don’t have a backup of your data.

Conclusion

In this tutorial, you have learned how to use the SQL TRUNCATE TABLE and DROP TABLE commands. Both are fairly easy to use, the syntaxes are:

• TRUNCATE TABLE table_name;
• DROP TABLE table_name;

Be careful when you use them.

But once you are done, you can go ahead and import your new data into your table (after TRUNCATE) or you can create a new table (after DROP).

• If you want to learn more about how to become a data scientist, take my 50-minute video course: How to Become a Data Scientist. (It’s free!)
• Also check out my 6-week online course: The Junior Data Scientist’s First Month video course.

Cheers,
Tomi Mester

Cheers,
Tomi Mester