# DELETE
The WHERE clause works in the same way as a select, so things like > , < , <> or LIKE can be used.
Notice: It is necessary to use conditional clauses (WHERE, LIKE) in delete query. If you do not use any conditional clauses then all data from that table will be deleted.
# Delete all rows from a table
This will delete everything, all rows from the table. It is the most basic example of the syntax. It also shows that DELETE statements should really be used with extra care as they may empty a table, if the WHERE clause is omitted.
# LIMITing deletes
This works in the same way as the ‘Delete with Where clause’ example, but it will stop the deletion once the limited number of rows have been removed.
If you are limiting rows for deletion like this, be aware that it will delete the first row which matches the criteria. It might not be the one you would expect, as the results can come back unsorted if they are not explicitly ordered.
# Multi-Table Deletes
MySQL’s DELETE statement can use the JOIN construct, allowing also to specify which tables to delete from. This is useful to avoid nested queries. Given the schema:
| id | name | gender |
|---|---|---|
| 1 | Kathy | f |
| 2 | John | m |
| 3 | Paul | m |
| 4 | Kim | f |
| id | ownerId | name | color |
|---|---|---|---|
| 1 | 1 | Rover | beige |
| 2 | 2 | Bubbles | purple |
| 4 | 1 | Rover2 | white |
If we want to remove Paul’s pets, the statement
can be rewritten as:
1 row deleted
Spot is deleted from Pets
p1 and p2 are aliases for the table names, especially useful for long table names and ease of readability.
To remove both the person and the pet:
2 rows deleted
Spot is deleted from Pets
Paul is deleted from People
# foreign keys
When the DELETE statement involes tables with a foreing key constrain the optimizer may process the tables in an order that does not follow the relationship. Adding for example a foreign key to the definition of pets
the engine may try to delete the entries from people before pets , thus causing the following error:
The solution in this case is to delete the row from people and rely on InnoDB ‘s ON DELETE capabilities to propagate the deletion:
2 rows deleted
Paul is deleted from People
Spot is deleted on cascade from Pets
Another solution is to temporarily disable the check on foreing keys:
# Basic delete
The WHERE clause is optional but without it all rows are deleted.
# DELETE vs TRUNCATE
(opens new window) all the data and reset AUTO_INCREMENT index. It’s much faster than DELETE FROM tableName on a huge dataset. It can be very useful during development/testing.
When you truncate a table SQL server doesn’t delete the data, it drops the table and recreates it, thereby deallocating the pages so there is a chance to recover the truncated data before the pages where overwritten. (The space cannot immediately be recouped for innodb_file_per_table=OFF .)
# Multi-table DELETE
MySQL allows to specify from which table the matching rows must be deleted
delete all from table
You can use the below query to remove all the rows from the table, also you should keep it in mind that it will reset the Identity too.
This should be faster:
because RDBMS don’t have to look where is what.
You should be fine with truncate though:
This is deletes the table table_name .
Replace it with the name of the table, which shall be deleted.
There is a mySQL bug report from 2004 that still seems to have some validity. It seems that in 4.x, this was fastest:
TRUNCATE table_name was DELETE FROM internally back then, providing no performance gain.
This seems to have changed, but only in 5.0.3 and younger. From the bug report:
[11 Jan 2005 16:10] Marko Mäkelä
I’ve now implemented fast TRUNCATE TABLE, which will hopefully be included in MySQL 5.0.3.
Is a DDL(Data Definition Language), you can delete all data and clean identity. If you want to use this, you need DDL privileges in table.
DDL statements example: CREATE, ALTER, DROP, TRUNCATE, etc.
Is a DML(Data Manipulation Language), you can delete all data. DML statements example: SELECT, UPDATE, etc.
It is important to know this because if an application is running on a server, when we run a DML there will be no problem. But sometimes when using DDL we will have to restart the application service. I had that experience in postgresql.
DELETE FROM и TRUNCATE TABLE в SQL
Оператор DELETE FROM используется для удаления строк в таблице. Например:
Здесь мы удаляем строку из таблицы Customers, где customer_id равен 5 .
Удалить все строки в таблице
С помощью оператора WHERE мы можем указать, какие строки следует удалить. Однако если нужно удалить сразу все строки, то достаточно просто не указывать оператор WHERE . Например:
Здесь мы удаляем все строки из таблицы Customers.
Примечание: Будьте осторожны при использовании оператора DELETE FROM . Записи могут быть безвозвратно утеряны, если у вас не окажется бэкапов (резервных копий).
Оператор TRUNCATE TABLE в SQL
Оператор TRUNCATE TABLE — это еще один способ одновременного удаления сразу всех строк в таблице. Например:
Здесь мы делаем то же самое, что и выше — удаляем все строки из таблицы Customers.
Примечание: Оператор TRUNCATE TABLE нельзя использовать в связке с оператором WHERE .
DELETE FROM против TRUNCATE TABLE в SQL
Основное различие между обоими операторами заключается в том, что оператор DELETE FROM может использоваться в связке с оператором WHERE , а TRUNCATE TABLE — нет. Это означает, что мы можем удалить одну или несколько строк, используя оператор DELETE FROM , в то время как оператор TRUNCATE TABLE удаляет сразу всё содержимое таблицы.
Мы можем выполнить работу оператора TRUNCATE TABLE оператором DELETE FROM , просто не указывая оператор WHERE . Например, следующий код:
How To Delete Data in SQL
In Structured Query Language, more commonly known as SQL, the DELETE statement is one of the most powerful operations available to users. As the name implies, DELETE operations irreversibly delete one or more rows of data from a database table. Being such a fundamental aspect of data management, it’s important for SQL users to understand how the DELETE statement works.
This guide will go over how to use SQL’s DELETE syntax to delete data from one or more tables. It will also explain how SQL handles DELETE operations that conflict with foreign key constraints.
Prerequisites
In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:
- A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
- MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.
Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.
You’ll also need a database and table loaded with some sample data which you can use to practice deleting data. We encourage you to read the following Connecting to MySQL and Setting up a Sample Database section for details on how to create a database and two tables which this guide will use in examples throughout.
Connecting To MySQL and Setting Up a Sample Database
If your SQL database system runs on a remote server, SSH into your server from your local machine:
Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:
Create a database named deleteDB :
If the database was created successfully, you’ll receive output like this:
To select the deleteDB database, run the following USE statement:
After selecting the deleteDB database, create a couple tables within it. As an example, imagine that you and some of your friends started a club in which members can share music equipment with one another. To help you keep track of club members and their equipment, you decide to create a couple of tables. The first table will have the following four columns:
- memberID : each club member’s identification number, expressed with the int data type. This column will also serve as the table’s primary key
- name : each member’s name, expressed using the varchar data type with a maximum of 30 characters
- homeBorough : this column will store the borough in which each member lives, again expressed using the varchar data type but with a maximum of only 15 characters
- email : the email address through which each member can be contacted, expressed using the varchar data type with a maximum of 30 characters
Create a table named clubMembers that has these four columns:
The next table will have the following columns:
- equipmentID : a unique identifier for each piece of equipment. Values in this column will be of the int data type. Like the memberID column in the clubMembers table, this column will serve as the table’s primary key
- equipmentType : what type of instrument or tool each row represents (e.g., guitar , mixer , amplifier , etc.). These values will be expressed using the varchar data type with a maximum of 30 characters
- brand : the brand that produced each piece of equipment, again expressed using the varchar data type with a maximum of 30 characters
- ownerID : this column will hold the ID number of the club member who owns the piece of equipment, expressed as an integer.
In order to ensure that the ownerID column only holds values that represent valid member ID numbers, you could create a foreign key constraint that references the clubMember table’s memberID column. A foreign key constraint is a way to express a relationship between two tables. A foreign key does this by requiring that values in the column on which it applies must already exist in the column that it references. In the following example, the foreign key constraint requires that any value added to the ownerID column must already exist in the memberID column.
Create a table with these columns and this constraint named clubEquipment :
Note that this example provides a name for the foreign key constraint, fk_ownerID . MySQL will automatically generate a name for any constraint you add, but defining one here will be useful when we need to reference this constraint later on.
Next, run the following INSERT INTO statement to load the clubMembers table with six rows of sample data:
Then run another INSERT INTO statement to load the clubEquipment table with twenty rows of sample data:
With that, you’re ready to follow the rest of the guide and begin learning about how to delete data with SQL.
Deleting Data from a Single Table
The general syntax for deleting data in SQL looks like this:
Warning: The important part of this syntax is the WHERE clause, as this is what allows you to specify exactly what rows of data should get deleted. Without it, a command like DELETE FROM table_name ; would execute correctly, but it would delete every row of data from the table.
Be aware that a successful DELETE operation is irreversible. If you were to run one without knowing exactly what data it will delete, there’s a chance that you could accidentally delete the wrong records. One way to help make sure you don’t accidentally delete the wrong data is to first issue a SELECT query to see what data will get returned by a DELETE operation’s WHERE clause.
To illustrate, let’s say you wanted to remove any records related to music equipment made by the brand Korgi. To be safe, though, you decide to first write a query to see exactly what equipment records list Korgi in their brand column.
To find what instruments in your table are made by Korg, you could run the following query. Note that unlike a SELECT query or an INSERT INTO operation, DELETE operations do not allow you to specify individual columns, as they’re intended to delete entire rows of data. To imitate this behavior, this query follows the SELECT keyword with an asterisk ( * ) which is SQL shorthand and represents “every column”:
This query returns every column from the clubEquipment table, but only returns rows whose brand column contains the value Korgi :
To delete this row you would run a DELETE operation that has FROM and WHERE clauses identical to the previous SELECT statement:
This output indicates that the DELETE operation only affected a single row. However, you can delete multiple rows of data with any WHERE clause that returns more than one row.
The following SELECT query returns every record in the clubEquipment table whose equipmentType column contains the word electric :
Again, to delete these four records, rewrite this query operation but replace SELECT * with DELETE :
You can also use subqueries to return and delete more granular result sets. A subquery is a complete query operation — meaning, an SQL statement that starts with SELECT and includes a FROM clause — embedded within another operation, following the surrounding operation’s own FROM clause.
Say, for example, that you wanted to delete any equipment listed in the clubEquipment table owned by any member whose name begins with the letter “L.” You could first query for this data with a statement like this:
This operation returns every row from the clubEquipment table whose ownerID column appears in the values returned by the subquery beginning on the fourth line. This subquery returns the memberID of any record whose name` value begins with “L”:
You could then remove this data with the following DELETE statement:
Deleting Data from Multiple Tables
You can delete data from more than one table in a single operation by including a JOIN clause.
JOIN clauses are used to combine rows from two or more tables into a single query result. They do this by finding a related column between the tables and sorting the results appropriately in the output.
The syntax for a DELETE operation that includes a JOIN clause looks like this:
Note that because JOIN clauses compare the contents of more than one table, this example syntax specifies which table to select each column from by preceding the name of the column with the name of the table and a period. This is known as a fully qualified column reference. You can specify which table a column should be selected from like this for any operation, although it’s not necessary when selecting only from a single table as we’ve done in the previous examples.
To illustrate deleting data with a JOIN clause, say your club decides to limit what brands of musical equipment members can share. Run the following statement to create a table named prohibitedBrands in which you will list what brands are no longer acceptable for the club. This table only has two columns, both using the varchar data type, to hold each brand’s name and what country they operate in:
Then load this new table with some sample data:
Following that, the club decides to delete any records of equipment from the clubEquipment table whose brands appear in the prohibitedBrands table and are based in the United States.
You could query for this data with an operation like the following SELECT statement. This operation joins the clubEquipment and prohibitedBrands tables together, only returning the rows whose brand and brandName columns share a common value. The WHERE clause refines this result set further by excluding any brand whose homeCountry column doesn’t include USA as its value:
That’s all the information we’re looking for; namely, each USA-based brand in the prohibitedBrands table that also appears in the clubEquipment table.
To delete these brands from the prohbitedBrands table and the associated equipment from clubEquipment , rewrite the previous SELECT statement but replace SELECT * with DELETE followed by the names of both tables:
This output indicates that the operation deleted four rows of data: two rows from clubEquipment and two rows from prohibitedBrands . If you only wanted to delete the records from the clubEquipment table and maintain all the records in the prohibitedBrands table, you would only list clubEquipment after the DELETE keyword, and vice versa.
Changing Foreign Key DELETE Behavior
By default, any DELETE statement that would cause a conflict with a foreign key will fail.
Recall from the Connecting to MySQL and Setting up a Sample Database section of the Prerequisites that the ownerID column of the clubEquipment table is a foreign key that references the ownerID column of the clubEquipment tabl. This means that any value entered into the ownerID column must already exist in the memberID column.
If you attempt to delete a row of data from the clubMembers table whose memberID value is used anywhere in the ownerID column, it will cause an error:
You can avoid this error by first removing any rows in the child table ( clubEquipment in this example) where the foreign key value exists in the parent table ( clubMembers ).
Alternatively, you can change this behavior by replacing the existing foreign key constraint with one that treats DELETE operations differently.
Note: Not every database management system or engine allows you to add or remove a constraint from an existing table as outlined in the following paragraphs. If you’re using an RDBMS other than MySQL, you should consult its official documentation to understand what limitations it has for managing constraints.
To replace the current constraint, you must first remove it with an ALTER TABLE statement. Recall that in the CREATE TABLE statement for clubEquipment , we defined fk_ownerID as a name for the table’s foreign key constraint:
Following that, create a new foreign key constraint that’s configured to treat DELETE operations in a way that makes sense for the given use case. Aside from the default setting which prohibits DELETE statements that violate the foreign key, there are two other options available on most RDBMSs:
- ON DELETE SET NULL : This option will allow you to delete records from the parent table, and will reset any values in the child table that reference them as NULL .
- ON DELETE CASCADE : When you delete a row in the parent table, this option will cause SQL to automatically delete any records that reference it in the child table.
For the purposes of this example, ON DELETE SET NULL doesn’t make sense. If a member leaves the club and their record is removed from the clubMembers table, their equipment is no longer available to the remaining members and should consequently be removed from the clubEquipment table. Therefore, the ON DELETE CASCADE option makes more sense for our purposes.
To add a foreign key constraint that follows the ON DELETE CASCADE behavior, run the following ALTER TABLE statement. This creates a new constraint named newfk_ownerID which replicates the previous foreign key definition, but includes the ON DELETE CASCADE option:
This output indicates that it impacted all seven remaining rows in the clubEquipment table.
Note: Instead of altering a table’s definition to change how a foreign key handles DELETE operations, you can define this behavior from the start in the CREATE TABLE statement like this:
Following that, you’ll be able to delete any record from the clubMembers table, and any row in the clubEquipment table that references it will also be deleted:
Although this output says it only affected one row, it will have also deleted any equipment records in the clubEquipment table that list their ownerID value as 6 .
Conclusion
By reading this guide, you learned how to delete data from one or more tables using the DELETE statement. You also learned how SQL handles DELETE operations that conflict with foreign key constraints, and how to change that default behavior.
The commands outlined here should work on any database management system that uses SQL. Keep in mind that every SQL database uses its own unique implementation of the language, so you should consult your DBMS’s official documentation for a more complete description of how it handles the DELETE statement and what options are available for it.
If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
Tutorial Series: How To Use SQL
Series Description
Structured Query Language — commonly known as SQL — is a language used to define, control, manipulate, and query data held in a relational database. SQL has been widely adopted since it was first developed in the 1970s, and today it’s the predominant language used to manage relational database management systems.
Ideal for managing structured data (data that can fit neatly into an existing data model), SQL is an essential tool for developers and system administrators in a wide variety of contexts. Also, because of its maturity and prevalence, candidates with SQL experience are highly sought after for jobs across a number of industries.
This series is intended to help you get started with using SQL. It includes a mix of conceptual articles and tutorials which provide introductions to various SQL concepts and practices. You can also use the entries in this series for reference while you continue to hone your skills with SQL.
Note: Please be aware that the tutorials in this series use MySQL in examples, but many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.