SQL - Merge trying to INSERT instead of UPDATE because of NULL columns

Question

Hello and good day to everyone. I am trying to execute the following sequence on two tables table2 and table1.

MERGE INTO table2 WITH (HOLDLOCK) AS target
    USING
    (
      SELECT column1,
             MAX(AccessDate) AS AccessDate,
             SUM(AccessCount) AS AccessCount,
             column4,
             column5, 
             column6, 
             column7, 
             column8
      FROM  table1
      GROUP BY column1, column4, column5, column6, column7, column8
    ) AS source
    ON target.column1 = source.column1 AND
       target.column5 = source.column5 AND
       target.column6 = source.column6 AND
       target.column7 = source.column7 AND
       target.column8 = source.column8
    WHEN MATCHED THEN 
    UPDATE SET target.LastAccessDate = source.AccessDate,
       target.LastWeeklyAccessCount = source.AccessCount
    WHEN NOT MATCHED BY TARGET THEN
       INSERT (column1, LastAccessDate, LastWeeklyAccessCount, column4, column5, column6, column7, column8)
        VALUES (source.column1, source.AccessDate, source.AccessCount, source.column4, source.column5, source.column6, source.column7, source.column8);

With the existing dates in table1 it does what it should do. If I insert another record in table1 with the JOIN conditions met, it tries to INSERT instead of UPDATE because of the NULL<>NULL when comparing the two tables and this comes up because of the UNIQUE constraint I have:

Cannot insert duplicate key in object 'dbo.table2'.

Can anyone help me fix this problem?

Would it work correctly if you match `null` to `null`: `((target.column5 = source.column5) OR (target.column5 IS NULL AND source.column5 IS NULL)) AND`. Simlarly for the other columns that can be null... — user1429080, Nov 24 '16 at 07:41
@BogdanPușcașu you can fix your issue using the user1429080 comment — gotqn, Nov 24 '16 at 08:16
Indeed, your solution worked! Thank you very much for helping me. — Bogdan Pușcașu, Nov 24 '16 at 10:56

score 1 · Accepted Answer · answered Nov 24 '16 at 08:58

One way of doing it:

ON target.column1 = source.column1 AND
   ISNULL(NULLIF(target.column5, source.column5), 
            NULLIF(source.column5, target.column5)) IS NULL AND
   ISNULL(NULLIF(target.column6, source.column6), 
            NULLIF(source.column6, target.column6)) IS NULL AND
   target.column7 = source.column7 AND
   ISNULL(NULLIF(target.column8, source.column8), 
            NULLIF(source.column8, target.column8)) IS NULL

NULLIF will return NULL if two values are the same, ISNULL will select the first non-null value (or NULL if both values are null) so if the result of ISNULL is NULL it means two values are the same (both NULL or same value).

score 0 · Answer 2 · answered Jun 21 '17 at 23:45

There is a great discussion of this at www.made2mentor.com. He suggests using EXCEPT since it handles NULLs better than all the alternatives. Snippet:

WHEN MATCHED AND EXISTS
    (SELECT Source.CustomerName, Source.Planet
     EXCEPT
     SELECT Target.CustomerName, Target.Planet)

SQL - Merge trying to INSERT instead of UPDATE because of NULL columns

2 Answers2