Bogdan Pușcașu Bogdan Pușcașu - 13 days ago 6
SQL Question

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

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?

Answer

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).