0

This question already has an answer here:

I'm using a query with merge that works fine if all columns in the ON clause have a value but WHEN MATCHED omits if there's a NULL mark in one of the columns:

MERGE PEPS AS peps
USING (Lots of Cols..
       FROM PEPS_temp) 
AS temp (Lots of Cols..) 
ON peps.[Name] = temp.[Name] AND 
   peps.[LastName] = temp.[LastName] AND 
   peps.[TypeEntity] = temp.[TypeEntity] AND
   peps.[Source] = temp.[Source]
WHEN MATCHED THEN
UPDATE SET peps.[Category] = temp.[Category] 
WHEN NOT MATCHED THEN
INSERT (Lots of Cols..) VALUES (temp.[Col], temp.[Col], temp[Col]....)

Case: I have target table that I update manually first its Category column so I set Category='EX BLOCKED' the source table is the updated list so the clause WHEN MATCHED is supposed to update the Category column to 'BLOCKED' if it exists in the source table, if not WHEN NOT MATCHED inserts the new rows that do not exist.

Query runs successfully and when checking the lists I detect duplicates:

;WITH Duplicados
     AS (Select ROW_NUMBER() OVER  (PARTITION BY Name, LastName, TypeEntity, Source ORDER BY ( SELECT 0)) RowNum, Name, LastName, TypeEntity, Source
         FROM [PEPS])
SELECT * FROM Duplicados
WHERE  RowNum > 1 AND Source='OFAC SDN';

All the duplicate rows contain NULL marks in at least one of the columns of the ON clause.

Example:

enter image description here

marked as duplicate by mustaccio, Michael Green, Vérace, McNets, LowlyDBA Dec 6 '18 at 16:43

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Well it is related but it wouldn't have solved my problem – Roberto Torres Dec 3 '18 at 22:45
  • Knowing NULL value comparison rules in an SQL database would help you solve the problem. – mustaccio Dec 3 '18 at 23:22
  • 1
    Note: if the answer resolved your problem, don't forget to accept it. – RDFozz Dec 4 '18 at 21:31
3

The predicate for MATCH doesn't fail, it behaves exactly like it is supposed to. NULL is not a value, NULL is a state that indicates there is no value. Therefore, any predicate with any comparison to NULL will resolve to 'UNKNOWN' and not to 'TRUE', causing the row to be considered as a non-match. To deal with that, you will need to decide on the logic and how you would like to evaluate the rows in case you have NULLs. For example, if It is only for last name, and you want to consider it a match, you may solve it by extending the predicate to handle the NULL:

MERGE PEPS AS peps
USING (Lots of Cols..
       FROM PEPS_temp) 
AS temp (Lots of Cols..) 
ON peps.[Name] = temp.[Name] AND 
   (peps.[LastName] = temp.[LastName] OR peps.LastName IS NULL) AND 
   peps.[TypeEntity] = temp.[TypeEntity] AND
   peps.[Source] = temp.[Source]
WHEN MATCHED THEN
UPDATE SET peps.[Category] = temp.[Category] 
WHEN NOT MATCHED THEN
INSERT (Lots of Cols..) VALUES (temp.[Col], temp.[Col], temp[Col]....)
  • "any comparison to NULL will resolve to 'UNKNOWN' and not to 'TRUE'" oh my god this is so logical why I didn't think like this it works now thanks this also applies in a LEFT JOIN query that I used to check already existing rows from target and source – Roberto Torres Dec 3 '18 at 18:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.