intro
Today, we’re looking into ways we can purge unnecessary data from our databases. Let’s learn everything we can to effectively delete data in SQL!
No matter if you’re a developer, DBA, or data analyst, chances are that you find yourself frequently working with data. And if you find yourself frequently working with data, you will sometimes inevitably need to delete some of it.
How to delete table data in SQL? That’s what we’re looking into today.
When to Delete Data Using SQL?
Before we talk about ways to delete table data in SQL, we need to understand when we should delete data using SQL. The most common scenarios are:
It’s worth noting that in many cases, “deleting” doesn’t mean deleting at all — in some applications, deleting means “marking” the data as deleted and perhaps moving it to another database/table so it’s no longer accessible by the application. This approach is called “soft delete.”
That’s why sometimes you may hear about applications that have deleted user data, but suffered a data breach and the data was still there.
How to Delete Data Using SQL?
Now that we know when we delete table data in SQL, we need to know how to do it too. Relational database management systems offer multiple ways to do so:
A basic way to delete data using the DELETE FROM SQL statement looks like so:
1
DELETE FROM your_table
2
WHERE [boolean_expression];
Instead, a TRUNCATE statement looks like so:
1
TRUNCATE [table_name];
A DELETE FROM statement has a boolean expression after the WHERE clause that specifies exactly what data needs to be deleted. A TRUNCATE statement doesn’t support such an expression, as it deletes all data in a table in an instant. Because of that, it doesn’t come with as much overhead when compared to the DELETE FROM SQL query or when deleting fractions of data.
To wipe fractions of data, one can also create a table that only has the necessary columns when using the original table. e.g. a query like so would create a table (table_x) at the same time only bearing select columns from another table (table_y):
1
CREATE TABLE table_x AS SELECT columns FROM table_y;
In this case, we’ve created a table data_table2 with only two columns from data_table . Provided our data_table consists of more columns than that (it does — see below), some of the data is wiped without us even needing to employ a DELETE clause.
In other words, we’ve just wiped 4 columns out of the data set without even using a DELETE query. Smart, isn’t it? Learn more about creating tables using another table.
No matter which way to delete data you choose, always keep in mind the fact that different storage engines within your DBMS may approach the process of deleting data differently. For example, if the file_per_table option isn’t specified, MySQL’s InnoDB will only “mark” the data as deleted and still hold it inside of its tablespace (the ibdata1 file) because you didn’t tell the storage engine to store data in different files!
Some storage engines may not “link” the data to tablespaces (MySQL’s MyISAM is a great example of that), and in that regard, you may be able to just delete files instead of running SQL queries, but that depends on your use case since such storage engines don’t come with ACID capabilities and are often prone to crashes.
Another way to delete data pertains to partitioning. A query like so would drop a partition in demo_table, and with that, delete all of the within that partition:
1
ALTER TABLE demo_table DROP PARTITION [partitions]
Such an approach may be especially useful for those using partitioning by RANGE because when such a partitioning type is in use, it’s even easier to remove data after identifying it. Find out more about archiving data using partitions.
Summary
That’s it, really — delete table data in SQL isn’t rocket science, and basic measures do indeed go a long way. Before deleting data, evaluate your use case, think about the data you need to delete carefully, and employ measures wisely.
Follow our blog for more news, read books on databases to educate yourself further, and we’ll see you in the next one.
FAQ
What Is the Best Way to Delete Table Data in SQL?
There is no one “best” way to delete data in a table. Since you have multiple ways to delete data:
When to Delete Data in a Database?
Consider deleting data in a database once it’s no longer needed, once it’s old, or once it’s decommissioned. Alternatively, delete data to comply with regulations such as GDPR.
