This post I will show you how you can create a MySQL DELETE subquery that returns more than 1 row as its result to use in a DELETE query.

MySQL

Data management is key when operating with a database. Proper table structure and data input could help promote a end user experience.

My experience has shown linking tables with like column names will help with administration when a nested or subquery is needed.

My Scenario

I had a few tables that I wanted to delete rows from. I needed the id from the rows in another table to identify which items needed to be deleted. A regular subquery was not going to work in this situation because the subquery potentially could return more than 1 row with ids.

IN OPERATOR

This is where the IN operator for the MySQL DELETE subquery comes in. If used, then the subquery could return multiple ids to use in the main query.

Example

Lets take the below tables. The first table is a list the user creates.

mysql> select * from lists;
+---------+---------------------+----------+
| list_id | title               | owner_id | 
+---------+---------------------+----------+
|     142 | Grocery             |      163 | 
|     143 | Hardware Store      |      148 | 
|     144 | Movies              |      148 | 
+---------+---------------------+----------+

The next table holds the items for the list above. They are linked using the list_id column.

mysql> select * from items;
+----+---------+----------------------------+
| id | list_id | item                       |
+----+---------+----------------------------+
| 39 |     142 | Eggs                       |
| 40 |     142 | Soup                       |
| 41 |     142 | Juice box                  |
| 42 |     142 | Milk                       |
| 51 |     143 | Hammer                     |
| 52 |     143 | Nails                      |
| 53 |     144 | Movie Title #1             |
| 54 |     144 | Movie Title #2             |
| 55 |     144 | Movie Title #3             |
+----+---------+----------------------------+

If I wanted to delete all the items that are associated with the lists from owner_id 148, I could use the below query

DELETE FROM items WHERE list_id IN (SELECT list_id FROM lists WHERE owner_id = 148);

The subquery will return two rows with the list_id for 143 and 144 and feed that to the main delete query to finish the operation.

Utilizing this approach could prevent from using multiple redundant MYSQL statements which means less overhead bloated coding.