DELETE With a SELECT Statement in the WHERE Clause

1. Overview

When working with databases, there are times when we need to remove specific records that meet certain conditions. While it’s possible to write conditions directly in the WHERE clause, another option is to identify the records through a selection process before deletion. This can be particularly useful when the criteria for deletion depend on values from other tables or subqueries.

In this tutorial, we’ll explore how to use the DELETE statement in combination with a SELECT query in the WHERE clause across different database systems, including MySQL, SQL Server, and PostgreSQL.

To keep things simple, we’ll use a single example table, but the syntax we’ll discuss can easily be applied to scenarios involving multiple tables.

2. Using DELETE With a SELECT in WHERE Clause

Here’s the structure of our test table with some sample data from the University database:

SELECT *
FROM Exam;
+------+----------+------------+-------+-----------+------------+
| id   | semester | exam_date  | grade | course_id | student_id |
+------+----------+------------+-------+-----------+------------+
|    1 | SPRING   | 2022-07-11 | A+    | CS111     |       1001 |
|    2 | SPRING   | 2022-07-11 | A     | CS121     |       1001 |
|    3 | SPRING   | 2022-07-11 | A+    | CS122     |       1001 |
|    4 | FALL     | 2022-12-05 | B+    | CS211     |       1001 |
[...]
|   10 | FALL     | 2023-12-05 | NULL  | CS121     |       1001 |
|   11 | FALL     | 2023-12-06 | NULL  | CS122     |       1001 |
[...]
143 rows in set (0,01 sec)

Our goal is to delete all exams that have NULL as their grade, which likely indicates exams that were scheduled but not taken by the student.

Before performing the DELETE operation, it’s a good practice to run the SELECT query to make sure it returns the correct records:

SELECT *
FROM Exam
WHERE grade IS NULL;
+------+----------+------------+-------+-----------+------------+
| id   | semester | exam_date  | grade | course_id | student_id |
+------+----------+------------+-------+-----------+------------+
|   10 | FALL     | 2023-12-05 | NULL  | CS121     |       1001 |
|   11 | FALL     | 2023-12-06 | NULL  | CS122     |       1001 |
|   12 | FALL     | 2023-12-14 | NULL  | MA411     |       1001 |
[...]
36 rows in set (0,00 sec)

After checking the results, we can proceed with the DELETE operation. We expect to have 143-36=107 rows remaining after the DELETE.

2.1. SQL Server and PostgreSQL

In SQL Server and PostgreSQL, we can include the subquery directly in the WHERE clause:

DELETE
FROM Exam
WHERE id IN
    (SELECT id
     FROM Exam
     WHERE grade IS NULL);

PostgreSQL outputs DELETE 36 and SQL Server (36 rows affected). Let’s look at the contents of the table again:

  id  | semester | exam_date  | grade | course_id | student_id 
------+----------+------------+-------+-----------+------------
[...]
    9 | SPRING   | 2023-07-11 | F     | MA211     |       1001
   21 | SPRING   | 2022-07-11 | A     | CS121     |       1003
   22 | SPRING   | 2022-07-11 | A+    | CS122     |       1003
[...]
(107 rows)

As expected, the NULL grade rows disappeared.

2.2. MySQL Alias

The intuitive approach of using a subquery directly in the WHERE clause, as used in PostgreSQL and SQL Server, doesn’t work with MySQL because MySQL doesn’t allow modifying a table and selecting from the same table in a subquery. This is documented in the Restrictions on Subqueries section of the official MySQL documentation, which states that “you cannot modify a table and select from the same table in a subquery“.

Let’s give it a try:

DELETE
FROM Exam
WHERE id IN
    (SELECT id
     FROM Exam
     WHERE grade IS NULL);
ERROR 1093 (HY000): You can't specify target table 'Exam' for update in FROM clause

MySQL requires the use of a temporary table, or alternatively, an alias, to handle this limitation. A temporary table is a special type of table that is created and used within the scope of a session or transaction and is automatically dropped when the session or transaction ends. However, to avoid the complexity of creating and managing a temporary table, we can use an alias for the subquery result instead.

By using an alias, we can first store the results of the subquery and then perform the DELETE operation on the main table using the stored results. This two-step process ensures that MySQL can handle the modification and selection operations without conflict:

DELETE
FROM Exam
WHERE id IN
    (SELECT id
     FROM
       (SELECT id
        FROM Exam
        WHERE grade IS NULL) AS TEMP);
Query OK, 36 rows affected (0,04 sec)

The AS keyword in the query provides an alias for the subquery result. In this context, AS TEMP names the subquery result set as TEMP. By creating an alias, MySQL treats the subquery as a separate, independent entity, allowing the main query to continue without problems.

Let’s check the Exam table again:

SELECT *
FROM Exam;
+------+----------+------------+-------+-----------+------------+
| id   | semester | exam_date  | grade | course_id | student_id |
+------+----------+------------+-------+-----------+------------+
[...]
|    9 | SPRING   | 2023-07-11 | F     | MA211     |       1001 |
|   21 | SPRING   | 2022-07-11 | A     | CS121     |       1003 |
|   22 | SPRING   | 2022-07-11 | A+    | CS122     |       1003 |
|   23 | SPRING   | 2022-01-11 | A+    | CS123     |       1003 |
[...]
107 rows in set (0,00 sec)

The result is as expected.

2.3. MySQL Temporary Table

There are cases where the subquery is so complex that using a temporary table is preferable over an alias. In such scenarios, the code is a bit longer and requires more steps:

• Create the temporary table
• Perform the DELETE operation using the temporary table
• Optionally, drop the temporary table

Here is the code:

-- Creating the temporary table
CREATE TEMPORARY TABLE TempExamIds AS
SELECT id
FROM Exam
WHERE grade IS NULL;

-- Performing the DELETE operation
DELETE
FROM Exam
WHERE id IN
    (SELECT id
     FROM TempExamIds);

-- (Optional) Dropping the temporary table
DROP TEMPORARY TABLE TempExamIds;

This way, we get a table modification identical to the previous one obtained with an alias.

By breaking the query into smaller steps, the main query becomes simpler and easier to understand. In addition, a temporary table allows us to isolate and modify parts of the query without affecting the entire process. This modularity improves maintainability and makes future updates or changes easier to manage.

3. Conclusion

In this article, we explored how to use the DELETE statement in combination with a SELECT query in the WHERE clause across different database systems, including MySQL, SQL Server, and PostgreSQL.

We started with SQL Server and PostgreSQL, where we can include the subquery directly in the WHERE clause. This method is simple and efficient, and results in the expected deletion.

As for MySQL, it doesn’t allow modifying a table and selecting from the same table in a subquery. To get around this limitation, we explored two approaches:

• using an alias
• employing a temporary table

Both methods ensure that MySQL can handle the DELETE and SELECT operations without conflict. The alias approach simplifies the process, while the temporary table method, although longer, provides greater flexibility and modularity.