Bogdan Pușcașu Bogdan Pușcașu - 1 year ago 75
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download