CODEWITHSUNDEEP
sql-servert-sql
hod
hod

Reputation: 761

Can't update table when comparing value to NULL

I have a #tempTable that contains all the necessary data which I want to use to update existing table:

UPDATE [BED].[Import].[InStore]
SET 
    A.Size = B.Size
    A.ProdDate = B.ProdDate
FROM [BED].[Import].[InStore] As A
    INNER JOIN #tmpTable as B
    ON A.Source = B.Source
    AND A.Location = B.Location
    AND A.Market = B.Market
    WHERE B.Location IS NOT NULL

But after adding this line at the very end, nothing gets updated:

AND (A.Size <> B.Size OR A.ProdDate <> B.ProdDate)

The columns A.Size and A.ProdDate are NULL in existing table, and the B.Size and B.ProdDate in #tmpTable have values like "King" and "2020-01-01". How can I make sure to update values that are NULL in existing table if the AND (A.Size <> B.Size OR A.ProdDate <> B.ProdDate) doesn't work?

Any help would be greatly appreciated.

Upvotes: 1

Views: 94

Answers (2)

Evan OJack
Evan OJack

Reputation: 577

If you always want to update the A table whenever the Size or ProdDate are NULL then you can use this in your where clause:

AND (A.Size <> B.Size OR A.ProdDate <> B.ProdDate OR A.Size IS NULL OR A.ProductDate IS NULL)

If the B table's Size and ProdDate are NULL then and you want them to overwrite values in the A table then you'll need a different approach. You can use the COALESCE function to change NULL values into something comparable:

AND (COALESCE(A.Size,-1) <> COALESCE(B.Size,-1) OR COALESCE(A.ProdDate,'1990-01-01') <> COALESCE(B.ProdDate,'1990-01-01'))

If you use the COALESCE method then you need to pay attention to your data types and what the expected values are within the table. You don't want to coalesce NULLs to a value that would occur within your table because then they won't get flagged as 'different' in the WHERE clause.

You should also read about the SQL Server ANSI_NULLS setting because this will change the behavior of your NULL comparisons.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You have NULL values. Almost any operation -- including comparisons -- returns NULL. And those are treated as "false".

So, your logic needs to be more verbose:

AND ( A.Size <> B.Size OR
      A.Size IS NOT NULL AND B.Size IS NULL OR
      A.Size IS NULL and B.SIZE IS NOT NULL OR
      A.ProdDate <> B.ProdDate OR
      A.ProdDate IS NOT NULL AND B.ProdDate IS NULL OR
      A.ProdDate IS NULL AND B.ProdDate IS NOT NULL
     )

Unfortunately, SQL Server does not support IS DISTINCT FROM, the standard NULL-safe comparison operator.

Upvotes: 3

Related Questions