SQL update statement - comparing values for nullable fields

Question

So we're using SQL Server in our DWH environment and we compare the values between the source and destination to update values accordingly. In some of the Merge statements we apply the match condition together with the intersect statement, however for some instances we use directly the update and check with the OR condition.

However, since sometimes we can have nullable fields which would need to be compared this is causing some issues.

We have tried to do the following 3 options:

• dest.col1 <> src.col1 - this is out of the question cause Nullables won't match each other
• isnull(dest.col1,0) <> isnull(src.col1,0) - also not an option since the fields can contain both nulls and zeros. In case something goes wrong and it would require a reload the values can be both null and numberic
• ((dest.col1 <> src.col1) OR (dest.col1 is null and src.col1 is not null) OR (dest.col1 is not null and src.col1 is null)) - also not an option since we have millions of records and this would entail more rows then necessary to be updated.

We could use a similar approach to the Merge statement with the match and intersect but we have noticed that's not optimal for millions of records.

Are there any other viable options that can be implemented?

Answer 1

Try this CASE WHEN code example:

    ;WITH CTE AS 
(
    SELECT *,
    CASE WHEN ColA IS NULL AND ColB IS NULL THEN 'Equal'
         WHEN ColB IS NOT NULL AND ColB IS NOT NULL 
                                               THEN CASE WHEN ColA = ColB THEN 'Equal'
                                               ELSE 'Not Equal' END
         WHEN ColA IS NULL AND ColB IS NOT NULL THEN 'Not Equal'
         WHEN ColB IS NULL AND ColA IS NOT NULL THEN 'Not Equal'
    END 'Comparison'
FROM YourTable
)                 

DELETE FROM CTE WHERE Comparison = 'Not Equal'