CODEWITHSUNDEEP
sqlsql-server
plditallo
plditallo

Reputation: 701

sql server: MERGE has unexpected results

The way these rows usually come into the target table the first time are with a sparse number of columns populated with mostly text data with the remainder of the columns set to NULL. On subsequent passes, the fresh data populates existing known (non null) and unknown (NULL) data. I've ascertained that the fresh data ( #pld) do indeed contain different data. The data does not appear to change. Here's what I have:

  BEGIN TRANSACTION

  BEGIN TRY


  MERGE INTO [metro].listings AS metroList
    USING #pld as listnew
      ON metroList.id = listnew.id
      AND metroList.sid = listnew.sid
      WHEN MATCHED AND (
        metroList.User != listnew.User
         or metroList.Email != listnew.Email
         or metroList.LocName != listnew.LocName
   ) THEN
   UPDATE SET
    metroList.User = listnew.User,
    metroList.Email = listnew.Email,
    metroList.LocName = listnew.LocName,
   WHEN NOT MATCHED THEN
  INSERT
   ( User,
     Email,
     LocName
   )
   VALUES
   (
     listnew.User,
     listnew.Email,
      listnew.LocName
    );

  COMMIT TRANSACTION

 END TRY


 IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

 END CATCH

I've tried replacing the != to under the update portion of the statement with <> . Same results. This has to be related to a comparison of a possible (likely) null value against a string--maybe even another null? Anyway, I'm calling on all sql-geeks to untangle this.

Upvotes: 3

Views: 237

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Also you can use option with NULLIF() function.

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

WHEN MATCHED AND (
                  NULLIF(ISNULL(metroList.[User],''), listnew.[User]) IS NOT NULL
                    OR NULLIF(ISNULL(metroList.Email, ''), listnew.Email) IS NOT NULL
                    OR NULLIF(ISNULL(metroList.LocName, ''), listnew.LocName) IS NOT NULL
                  )
THEN

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453067

As I understand the question you are looking for an expression that emulates IS DISTINCT FROM.

The answer you have accepted is not correct then

WITH metroList([User])
     AS (SELECT CAST(NULL AS VARCHAR(10))),
     listnew([User])
     AS (SELECT 'Foo')
SELECT *
FROM   metroList
       JOIN listnew
         ON NULLIF(metroList.[User], listnew.[User]) IS NOT NULL

Returns zero rows. Despite the values under comparison being NULL and Foo.

I would use the technique from this article: Undocumented Query Plans: Equality Comparisons

WHEN MATCHED AND EXISTS (
                         SELECT  metroList.[User], metroList.Email,metroList.LocName
                         EXCEPT
                         SELECT  listnew.[User], listnew.Email,listnew.LocName
                         )

Upvotes: 2

bhamby
bhamby

Reputation: 15450

Comparing NULL with an empty string will not work.

If either side could be NULL, you could do something like: 

WHEN MATCHED AND (
        COALESCE(metroList.User, '')    <> COALESCE(listnew.User, '')
     or COALESCE(metroList.Email, '')   <> COALESCE(listnew.Email, '')
     or COALESCE(metroList.LocName, '') <> COALESCE(listnew.LocName, '')
   ) THEN

Of course, this assumes that you're fine with NULL meaning the same as an empty string (which may not be appropriate).

Take a look at this BOL article on NULL comparisons.

Upvotes: 2

Related Questions