How To Delete Data in SQL

Introduction

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.

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:

ssh sammy@your_server_ip

Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

mysql -u sammy -p

Create a database named deleteDB:

CREATE DATABASE deleteDB;

If the database was created successfully, you’ll receive output like this:

Output
Query OK, 1 row affected (0.01 sec)

To select the deleteDB database, run the following USE statement:

USE deleteDB;

Output
Database changed

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:

CREATE TABLE clubMembers (
memberID int PRIMARY KEY,
name varchar(30),
homeBorough varchar(15),
email varchar(30)
);

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:

CREATE TABLE clubEquipment (
equipmentID int PRIMARY KEY,
equipmentType varchar(30),
brand varchar(15),
ownerID int,
CONSTRAINT fk_ownerID
FOREIGN KEY (ownerID) REFERENCES clubMembers(memberID)
);

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:

INSERT INTO clubMembers
VALUES
(1, 'Rosetta', 'Manhattan', 'hightower@example.com'),
(2, 'Linda', 'Staten Island', 'lyndell@example.com'),
(3, 'Labi', 'Brooklyn', 'siffre@example.com'),
(4, 'Bettye', 'Queens', 'lavette@example.com'),
(5, 'Phoebe', 'Bronx', 'snow@example.com'),
(6, 'Mariya', 'Brooklyn', 'takeuchi@example.com');

Then run another INSERT INTO statement to load the clubEquipment table with twenty rows of sample data:

INSERT INTO clubEquipment
VALUES
(1, 'electric guitar', 'Gilled', 6),
(2, 'trumpet', 'Yemehe', 5),
(3, 'drum kit', 'Purl', 3),
(4, 'mixer', 'Bearinger', 3),
(5, 'microphone', 'Sure', 1),
(6, 'bass guitar', 'Fandar', 4),
(7, 'acoustic guitar', 'Marten', 6),
(8, 'synthesizer', 'Korgi', 4),
(9, 'guitar amplifier', 'Vax', 4),
(10, 'keytar', 'Poland', 3),
(11, 'acoustic/electric bass', 'Pepiphone', 2),
(12, 'trombone', 'Cann', 2),
(13, 'mandolin', 'Rouge', 1),
(14, 'electric guitar', 'Vax', 6),
(15, 'accordion', 'Nonher', 5),
(16, 'electric organ', 'Spammond', 1),
(17, 'bass guitar', 'Peabey', 1),
(18, 'guitar amplifier', 'Fandar', 3),
(19, 'cello', 'Yemehe', 2),
(20, 'PA system', 'Mockville', 5);

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:

DELETE FROM table_name
WHERE conditions_apply;

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”:

SELECT * FROM clubEquipment
WHERE brand = 'Korgi';

This query returns every column from the clubEquipment table, but only returns rows whose brand column contains the value Korgi:

Output
+-------------+---------------+-------+---------+
| equipmentID | equipmentType | brand | ownerID |
+-------------+---------------+-------+---------+
|           8 | synthesizer   | Korgi |       4 |
+-------------+---------------+-------+---------+
1 row in set (0.00 sec)

To delete this row you would run a DELETE operation that has FROM and WHERE clauses identical to the previous SELECT statement:

DELETE FROM clubEquipment
WHERE brand = 'Korgi';

Output
Query OK, 1 row affected (0.01 sec)

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:

SELECT * FROM clubEquipment
WHERE equipmentType LIKE '%electric%';

Output
+-------------+------------------------+-----------+---------+
| equipmentID | equipmentType          | brand     | ownerID |
+-------------+------------------------+-----------+---------+
|           1 | electric guitar        | Gilled    |       6 |
|          11 | acoustic/electric bass | Pepiphone |       2 |
|          14 | electric guitar        | Vax       |       6 |
|          16 | electric organ         | Spammond  |       1 |
+-------------+------------------------+-----------+---------+
4 rows in set (0.00 sec)

Again, to delete these four records, rewrite this query operation but replace SELECT * with DELETE:

DELETE FROM clubEquipment
WHERE equipmentType LIKE '%electric%';

Output
Query OK, 4 rows affected (0.00 sec)

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:

SELECT *
FROM clubEquipment
WHERE ownerID IN
(SELECT memberID FROM clubMembers
WHERE name LIKE 'L%');

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 memberIDof any record whosename` value begins with “L”:

Output
+-------------+------------------+-----------+---------+
| equipmentID | equipmentType    | brand     | ownerID |
+-------------+------------------+-----------+---------+
|          12 | trombone         | Cann      |       2 |
|          19 | cello            | Yemehe    |       2 |
|           3 | drum kit         | Purl      |       3 |
|           4 | mixer            | Bearinger |       3 |
|          10 | keytar           | Poland    |       3 |
|          18 | guitar amplifier | Fandar    |       3 |
+-------------+------------------+-----------+---------+
6 rows in set (0.00 sec)

You could then remove this data with the following DELETE statement:

DELETE FROM clubEquipment
WHERE ownerID IN
(SELECT memberID FROM clubMembers
WHERE name LIKE 'L%');

Output
Query OK, 6 rows affected (0.01 sec)

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:

DELETE table_1, table_2
FROM table_1 JOIN table_2
ON table_2.related_column = table_1.related_column
WHERE conditions_apply;

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:

CREATE TABLE prohibitedBrands (
brandName varchar(30),
homeCountry varchar(30)
);

Then load this new table with some sample data:

INSERT INTO prohibitedBrands
VALUES 
('Fandar', 'USA'),
('Givson', 'USA'),
('Muug', 'USA'),
('Peabey', 'USA'),
('Yemehe', 'Japan');

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:

SELECT * 
FROM clubEquipment JOIN prohibitedBrands
ON clubEquipment.brand = prohibitedBrands.brandName
WHERE homeCountry = 'USA';

Output
+-------------+---------------+--------+---------+-----------+-------------+
| equipmentID | equipmentType | brand  | ownerID | brandName | homeCountry |
+-------------+---------------+--------+---------+-----------+-------------+
|           6 | bass guitar   | Fandar |       4 | Fandar    | USA         |
|          17 | bass guitar   | Peabey |       1 | Peabey    | USA         |
+-------------+---------------+--------+---------+-----------+-------------+
2 rows in set (0.00 sec)

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:

DELETE clubEquipment, prohibitedBrands
FROM clubEquipment JOIN prohibitedBrands
ON clubEquipment.brand = prohibitedBrands.brandName
WHERE homeCountry = 'USA';

Output
Query OK, 4 rows affected (0.01 sec)

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:

DELETE FROM clubMembers
WHERE memberID = 6;

Output
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

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.

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:

ALTER TABLE clubEquipment
DROP FOREIGN KEY fk_ownerID;

Output
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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:

ALTER TABLE clubEquipment
ADD CONSTRAINT newfk_ownerID
FOREIGN KEY (ownerID)
REFERENCES clubMembers(memberID)
ON DELETE CASCADE;

Output
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

This output indicates that it impacted all seven remaining rows in the clubEquipment table.

CREATE TABLE clubEquipment (
equipmentID int PRIMARY KEY,
equipmentType varchar(30),
brand varchar(15),
ownerID int,
CONSTRAINT fk_ownerID
FOREIGN KEY (ownerID) REFERENCES clubMembers(memberID)
ON DELETE CASCADE
);

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:

DELETE FROM clubMembers
WHERE memberID = 6;

Output
Query OK, 1 row affected (0.00 sec)

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.