Follow me. sql: Deleting data from a child list

In this article I want to describe how to delete columns from a table, and the columns to be deleted are also dependent on the criteria of other tables. To solve this problem, you need an application that is smart and fully compliant with the SQL92 subquery declaration.

I must remind readers that while queries may comply with SQL standards, many database manufacturers implement SQL with different syntax support. The following solution should be appropriate for most databases, but if you have a discrepancy, you should still look at the document. Also, since this query handles the delete declaration, you should test it on the experimental data before applying it to a real production environment.

Need more background information?

Check out these articles to get to the road quickly:

The SQL base I lookup data query involves the use of the delete query.

Using SQL sub-options to merge queries explains that child option queries can reduce the number of requests to the database and provide examples.

The SQL Basics: Querying multiple tables provides more information about child options, as well as a variety of other ways to access multiple tables using a single query.

Example of a pet shop

To explain how to do this type of column deletion, I'll use a table of the following database, called Petstore, that contains the list (inventory) information. In table A, called "variety (breed)", I store information about each animal and the inventory of the pet store. In table B, called "Checklist," contains information about a particular animal in the store.

In this case, let's assume that the store has sold out the whole litter of shitzu puppies. I can use the breed_id field in the breed table to delete all the items in the Shitzu list, like this:

DELETE from inventory WHERE breed_id

(SELECT breed_id from breed WHERE breed_name = ' Shitzu ');

First, I want to specify the form where the record needs to be deleted, and here is the list table. The recognition field breed_id is then repeatedly compared to the result of the same child option clause. I know what to look for is Shitzus, so I can delete them directly, instead of querying breed_id in a separate request.

What I have to warn you is that using the Delete declaration in this way is dangerous and can only be used if you are familiar with the structure of the database. The delete query deletes all columns from the affected table, and you should know what this means for the data you are managing. A good idea is to use the phrase Selete * Instead of the DELETE keyword to test the subquery results for the Delete declaration, so you can guarantee that the result contains everything you want to delete, like this:

SELECT * FROM inventory WHERE breed_id

(SELECT breed_id from breed WHERE breed_name = ' Shitzu ');

Delete and join Union

Someone asked for another possible solution to this problem: combine the join clause with the delete declaration. Since I have not used this method before, I have studied it and found that the document declaration of SQL Server supports this method, although it does not conform to SQL92. After testing and questioning the various database platforms, I found that combining the delete and join declarations was not working on any platform I had tested.

Delete one time from multiple tables

The above solution does not explain how to use the parent table to delete information from multiple children. However, the SQL92 specification does not provide a standard solution for accomplishing this task.

The declaration of Delete cannot accept multiple tables as one parameter. As a destructive query, this ensures that no ambiguity occurs where the command is to be executed. In addition, this restriction prevents the use of and and multiple subqueries within a single declaration. If you test the results of a select declaration to check which data the delete query will affect, you will find that the select Returns a list of multiple tables, and that the delete does not affect more than one subquery.

There are a number of possible ways to meet your needs, such as creating a field in a table that indicates whether the item is active. Alternatively, you can use the stored procedures in some databases to iterate over each of the required delete queries.