Скачать или смотреть How to Delete Records from a Table in SQL Server Based on a Temporary Table

Learn how to effectively delete records from your SQL Server table that do not match entries in a temporary table, ensuring only desired data remains.
---
This video is based on the question https://stackoverflow.com/q/63511758/ asked by the user 'vivek v' ( https://stackoverflow.com/u/8486579/ ) and on the answer https://stackoverflow.com/a/63511810/ provided by the user 'LONG' ( https://stackoverflow.com/u/6901788/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: I want to delete records from my table which is not present in temporary table

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Deleting Unwanted Records in SQL Server

Managing data in a database can sometimes be tricky, especially when it comes to ensuring that only the necessary records remain. If you've ever found yourself needing to delete records from a table that do not match those in a temporary table, you're not alone. This question is a common challenge among database administrators and developers.

In this post, we will address how to delete all records in a main table that do not exist in a temporary table, with a specific focus on SQL Server. We will walk through a practical example to illustrate the solution step by step.

The Problem

Imagine you have a primary table called ImageTable that contains various images. For instance, your ImageTable looks like this:

[[See Video to Reveal this Text or Code Snippet]]

You also have a temporary table called Temp_Image, which may contain a subset of your ImageTable like so:

[[See Video to Reveal this Text or Code Snippet]]

Your goal is to retain only specific records in the ImageTable that match the Temp_Image. In this case, you want to keep only the record with Id = 5252 and Image_Id = 100 from ImageTable, and delete all other records that have Id = 5252.

After processing, the expected result for ImageTable would be:

[[See Video to Reveal this Text or Code Snippet]]

The Solution

To achieve this, you can use a SQL query that involves a DELETE statement combined with JOIN, which allows you to effectively identify records that do not have corresponding entries in your temporary table. Here’s how you can do it:

Step-by-Step SQL Command

Writing the DELETE Statement: You will be deleting from the ImageTable using an INNER JOIN with Temp_Image to ensure you are only looking at relevant records.

Setting Up the Join Conditions: You will join Temp_Image with ImageTable on the Id and then use a LEFT JOIN to identify records in ImageTable that do not match Image_Id in Temp_Image.

Executing the WHERE Clause: Use a condition in the WHERE clause to specify that you want to delete records where a match is not found.

Here’s the SQL code that encapsulates this logic:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the SQL Code

DELETE A: This indicates that you are deleting from the table alias A (which represents ImageTable).

INNER JOIN: Ensures that only records matching the Id between ImageTable and Temp_Image will be considered.

LEFT JOIN: Used to fetch rows from ImageTable that don't have a corresponding Image_Id in Temp_Image.

WHERE C.Image_Id IS NULL: This condition filters the rows that do not have corresponding entries, ensuring that the unnecessary records are deleted.

Conclusion

By following these steps, you can efficiently delete the unwanted records from your ImageTable based on the presence of data in your temporary table. This method is not only straightforward but also ensures data integrity by keeping only the relevant records you need.

If you follow this guide and run the provided SQL command, you should achieve your desired result seamlessly. Happy coding!