Скачать или смотреть How to Execute an UPDATE Query for NULL Values in SQL Server

Learn how to update NULL values in your SQL Server database effectively by using correlated subqueries and the HAVING clause.
---
This video is based on the question https://stackoverflow.com/q/65721184/ asked by the user 'saz' ( https://stackoverflow.com/u/1100221/ ) and on the answer https://stackoverflow.com/a/65721478/ provided by the user 'Charlieface' ( https://stackoverflow.com/u/14868997/ ) 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: How do I write this update query?

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.
---
Understanding the Challenge of Updating NULL Values in SQL Server

In SQL Server databases, handling NULL values can often be challenging, especially when you want to ensure data integrity during updates. A common scenario is needing to update rows where a specific column—like product_type—is NULL, using values from other rows based on certain criteria. This guide walks you through a practical example and provides a step-by-step solution for performing such an update.

The Problem Statement

You may have a table named Call_Data, as demonstrated in the example below, which contains a product_type column that sometimes has NULL values. Your goal is to fill in these NULL values intelligently—where the corresponding skill has only one associated product_type—thereby maintaining consistency in your data.

Example Without NULL Filtering

Here are the records you might retrieve when executing a query to select product_type and skill:

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

You want to transform this to:

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

But, without careful structuring, your update may end up influencing a broader set of rows, many of which may not meet the criteria needed to ensure the integrity of your data.

The Steps to Write an Update Query

1. Set Up the UPDATE Statement

You need to create an UPDATE statement that allows you to select values based on a separate, correlated subquery. This approach ensures that you fetch the product_type directly linked to the corresponding skill value.

2. Constructing the Subquery

Using a CROSS APPLY enables you to define the relationship between the main query row and the subquery row. Here’s how you can construct this portion of the query:

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

GROUP BY: This clause groups the results by product_type.

HAVING COUNT(*) = 1: Ensures that you select only those product_type entries that are uniquely associated with each skill.

3. Full UPDATE Statement

Putting it all together, the complete SQL update query looks like this:

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

Explanation of the SQL Code

UPDATE c1: Updates the Call_Data table with an alias c1.

SET product_type = c2.product_type: Sets the product_type in the main table based on what’s returned from the subquery.

WHERE product_type IS NULL: Ensures that only rows with NULL product_type are being updated based on the criteria established in the subquery.

Final Thoughts

By employing this structured query, you can effectively update NULL values in your SQL Server databases, ensuring that data integrity and accuracy are upheld. The combination of CROSS APPLY and the HAVING clause allows for targeted updates that prevent unintended data changes.

Armed with this knowledge, you can now confidently tackle similar challenges in your own database work!