Data Management

SQL basics: Deleting from child tables

This SQL basics article responds to Builder.com members' questions about deleting rows from one table and using another table as a data reference.


I’ve received several requests for a description of how to delete rows from tables that are dependent on criteria from other tables. The solution lies in a clever, but completely SQL92-compliant application of subselect, or subquery statements.

I must remind readers that although a query may be compliant, various database manufacturers implement SQL with different syntax support. The solution below should function for most databases; however, you should always consult your documentation if your results vary. Also, because this query deals with the DELETE statement, you should test the query on test data before using it in a production environment.

Need more background information?
Check out these Builder.com articles to quickly bring you up to speed:

Pet store example
To explain how to perform this type of row deletion, I’ll use the following tables in a sample database called PetStore that contains inventory information. In Table A, breed, I’m storing information about each breed of animal the pet store stocks. Table B, inventory, contains information about the specific animals they have in the store.

Table A
breed_id breed_name breed_descr
1 Poodle Dog
2 Shitzu Dog
3 Saint Bernard Dog
4 Siamese Cat
5 Fancy Rat
breed

Table B
pet_id breed_id pet_name pet_gender pet_descr
100 1 Prince M Black
101 2 Toto F Tan
102 2 Spike M Tan
103 4 Marla F Seal tip
104 5 Narf M Grey
105 2 Fanny F Brown
inventory

For this example, let's assume the store has sold its entire litter of Shitzu puppies. I can use the breed_id field from the breed table to remove all Shitzu inventory entries like this:
DELETE FROM inventory WHERE breed_id IN
(SELECT breed_id FROM breed WHERE breed_name = ‘Shitzu’);


First, I specify the table from which to delete records, in this case, the inventory table. Then, I cross-check an identifying field, breed_id, against the results from a subselect clause. I know I’m looking for Shitzus, and I can remove them without having to look up the breed_id in a separate request.

I must warn you that using DELETE statements in this manner can be dangerous, and they should only be used if you're familiar with the database’s structure. The DELETE query will remove entire rows from the affected tables, and you should know what that means to the data you’re housing. It’s a good idea to test DELETE statements on subqueried results by replacing the keyword DELETE with the phrase SELECT * to ensure the results contain everything you want deleted, like this:
SELECT * FROM inventory WHERE breed_id IN
(SELECT breed_id FROM breed WHERE breed_name = ‘Shitzu’);


The mythic DELETE with JOIN
An alternate approach to this problem that a member specifically asked about was the use of JOIN clauses with DELETE statements. Not having used that solution before, I looked into it and found SQL Server documentation claiming this was supported, although not SQL92-compliant. Upon testing and consultation with gurus from various database platforms, I found that the use of DELETE with JOIN statements doesn't work on any of the platforms tested.

Deleting from multiple tables at once
The solution above still doesn't explain how to use a parent table to delete information from several child tables. But there is no standard solution provided within the SQL92 specification to accomplish this task.

The DELETE statement cannot accept multiple tables as a parameter. As a destructive query, this ensures that there is no ambiguity where the commands are to be executed. Additionally, this limitation prevents the use of AND with multiple subqueries within a single statement. If you test the results of your SELECT statement to see what data set would be affected in a DELETE query, you’ll find that SELECT will return results for a list of multiple tables and against multiple subqueries where DELETE will not.

There are a number of possible workarounds that may meet your needs, though, such as creating a field in your table that designates whether the entry is active or not. Alternatively, you can use a stored procedure in some databases to iterate through each required DELETE query.

Query questions?
Do you have a different answer for this question, or do you have another data handling scenario you’d like a solution for? Post your comments in the discussion area below or send our editors an e-mail.

 

Editor's Picks