SQL statements use left Outer Join instead of in to delete multiple records

SQL statements use left Outer Join instead of in to delete multiple records

Using the in statement as a condition for deleting multiple records is not only inefficient, but also limited by the maximum number of in sets (1000, an error will be reported if this number is exceeded, if the in statement is used as the condition for deleting multiple records, there is a great risk. To illustrate this problem, define the following two tables for a simple test.

Definition of "faculty table"

Create Table [DBO]. [Dep] (

[Depid] [int] identity (1, 1) not null,

[Name] [char] (20) null,

[Maxmember] [int] Null

) On [primary]

 

"Student table" Definition

Create Table [DBO]. [Student] (

[ID] [int] identity (1, 1) not null,

[Depid] [int] Null,

[Name] [nvarchar] (10) null,

[Age] [int] Null

) On [primary]

 

Now, we need to delete the records of all student tables associated with the maxmember faculty tables greater than 50.

 

Use the in deletion method:

Delete from student where ID

In (select a. ID as ID from student a left Outer Join Dep B on A. depid = B. depid where B. maxmember> 50)

Restrictions: 1. Low Efficiency

2. restricted by the maximum number of in sets being 1000.

 

Solution: Use left Outer Join:

Delete A from student a left Outer Join Dep B on A. depid = B. depid where B. maxmember> 50