SQL correctly deletes user-expired data

What is the right way to delete outdated data? First confessed to the premise, XX website has a rest assured that the enterprise zone, for super members can be assured enterprises, can be assured that the enterprise may set the recommended job display in the Assured Enterprise zone, information is stored in the info table, The so-called recommendation position is to write information from the info table to the recommended job list (recommposition), and the user for the super member is valid, for Super members will write a new record to the member processing record (Packagerecord), Do not change the user information, the use of associated user tables and members of the record table to achieve, because there are a lot of things like this, some products are not suitable will be eliminated, if each new product is in the user table in the new field to identify, so over time the user table field will be very redundant, Therefore, it is implemented by associating with the corresponding table, which avoids the difficulty of later maintenance. well, the return to the point, when the user handles the super member expires, the system needs to automatically clear out the member expires the user set up the recommended position information, because the recommended position table records are written in the info table, the information is actually stored in the info table, The recommended job table is just the ID of the associated info table, professional, regional, and so on to filter, so the recommended position table is equivalent to a temporary table, the records in the table can be physically deleted. Of course, if you want to do data analysis, you can also take the logical deletion.

To remove the recommended job information for a member's expired user settings:

Delete [recommposition] where [UserId] inch(    Select [UserId]  from [Packagerecord] where [Packagepid]=1  and [EndTime]<GETDATE())

At first glance, it seems that there is no problem, but I overlooked a problem, because the members of the record table is recorded in the sales of the individual members, will be taken to do financial statistics and data analysis, this table record is not deleted, so there may be a user has more than one record, so I need to go to the user ID to heavy. If the super member is valid for one months, there are users before the super member, with the use of the effect is still good this month and want to continue to handle, this one user has a number of members to record, encountered this situation we above the SQL statement will mistakenly delete a portion of the user's recommended position. Because there are expired members of the record and the non-expiry of the membership records of the user he is in fact belong to the Trust enterprise. So we can only remove the recommended job information that the super member has expired and has not continued to process the super member's user settings.

Remove the recommended job information that the super member has expired and has not continued to process the super member's user settings:

--Delete the expired user and not the user who has not expired.Delete [recommposition] where [UserId] inch(    Select distinct [UserId]  from [Packagerecord] where [Packagepid]=1  and [EndTime]<GETDATE()) and [UserId]  not inch(    Select distinct [UserId]  from [Packagerecord] where [Packagepid]=1  and [EndTime]>=GETDATE())

The deletion is not wrong, but the above SQL used in the not, the efficiency is not very high, and the SQL statement is not good understanding, we will try to optimize the optimization.

Delete the user's information, but as long as he has a valid membership record can not be deleted, that is, we can only delete those who have the maximum expiry time of the expiration time is less than the current time of the user's recommended position information .

Delete [recommposition] where [UserId] inch(    Select [UserId]  from [Packagerecord] where [Packagepid]=1 Group  by [UserId]  having MAX([EndTime])<GETDATE())

Well, write here this blog is almost, feel write a bit too verbose, slowly improve it!

Summary: 1, do not think too simple questions, as far as possible to consider a comprehensive point!

2, sometimes many think of a step, you can harvest a lot, slowly also progress, refueling, Xiao Rookie!  

SQL correctly deletes user-expired data