Nulls and the MERGE statement: I need to set a value to infinity. How?

32,772

Solution 1

You can use

WHEN MATCHED AND EXISTS (SELECT tgt.C EXCEPT SELECT src.C)

See this article for more on this issue.

Solution 2

You can change the ON part of the merge statement, putting in a check for when both source and target are null.

MERGE tgt
USING src
ON ( -- enter non-nullable columns to match on ...
    tgt.A = src.A
    AND (tgt.C = src.C OR (tgt.C IS NULL AND src.C IS NULL))
)
WHEN MATCHED -- ...

Solution 3

Actually, this works better. Just add another substitution value as an OR :-

WHEN MATCHED AND 
    ( 
    NOT (IsNull(tgt.C, 0) = IsNull(src.C, 0)) OR NOT (IsNull(tgt.C, 1) = IsNull(src.C, 1)) 
    ) 
THEN ....

Solution 4

Have you tried SET ANSI_NULLS OFF, which will make NULL=NULL return true? This may create additional issues but it could be a script-level workaround (turn it off then on once you run your proc).

Solution 5

WHEN MATCHED AND tgt.c <> src.c OR tgt.c IS NULL AND src.c IS NOT NULL OR tgt.c IS NOT NULL AND src.c IS NULL

View more solutions

32,772

IamIC

An artistic engineer & humanitarian at heart. As a technology architect, I create solutions which take the confusions out of things. I love to help people.

Updated on April 19, 2020

Comments

IamIC about 4 years
In SQL Server 2008, I'm using MERGE. Everything is fine except that I have 2 nullable columns. If I pass a null value and the target isn't null, MERGE doesn't see a difference (evals against null = false per BOL). If I use IsNull on both sides (source & target) that works, but has the issue of potentially mis-evaluating a value.

What I mean by the last statement is, if I say:
```
WHEN MATCHED AND NOT (IsNull(tgt.C, 0) = IsNull(src.C, 0)) THEN
```
then if tgt.C is null and src.C = 0, no update will be performed. No matter what substitute value I choose, I'll have this problem.

I also tried the "AND NOT (...true...)" syntax since BOL states that evaluations against null result in FALSE. However, it seems they actually result in NULL and do not result in my multi-part statement becoming false.

I thought one solution is to use NaN or -INF or +INF since these are not valid in target. But I can't find a way to express this in the SQL.

Any ideas how to solve this?

EDIT:

The following logic solves the problem, but it's verbose and won't make for fast evals:
```
declare @i int, @j int

set @j = 0
set @i = 0

if ISNULL(@i, 0) != ISNULL(@j, 0) OR 
    ((@i is null or @j is null) and not (@i is null and @j is null))
    print 'update';
```
IamIC over 13 years

BOL advises that ANSI NULLS are being deprecated.
IamIC over 13 years

That will perform a needless update if both src & tgt are null.
IamIC over 13 years

Isn't there a way to say INF?
IamIC over 13 years

"WHEN MATCHED AND tgt.c <> src.c OR ((tgt.c IS NULL OR src.c IS NULL) AND NOT (tgt.c IS NULL AND src.c IS NULL))" works, but what a mess.
Quassnoi over 13 years

@IanC: yes it is a mess. PostgreSQL supports IS NOT DISTINCT FROM and MySQL supports NOT <=> (both of which treat NULL as distinct comparable values), but SQL Server has nothing like that.
Quassnoi over 13 years

@IanC: SQL Server does not support Inf. You can pick any other value that is out of your domain; but if you don't have one, you will need to use the ternary logic (the NULL mess).
IamIC over 13 years

Well, one can tell SQL Server to do this by turning ANSI NULLS off, but that breaks SQL-92 compliance and the ability is being deprecated. Doing so makes it behave the same as PostgreSQL & MySQL. It's impossible to say infinity?? Crazy.
IamIC over 13 years

@Quassnoi I have decided rather to alter the domain and disallow the nulls.
IamIC over 13 years

I thought of that... the problem is almost any number could be in the domain. Even if I did -0.0000000001... that still has some element of risk.
Dawn over 13 years

In that case, use the value you substitute in the clause too, eg :-
Dawn over 13 years

WHEN MATCHED AND (NOT (IsNull(tgt.C, 123456789) = IsNull(src.C, 123456789)) OR (tgt.C = 123456789 AND src.C ! = 123456789) OR (tgt.C ! = 123456789 AND src.C = 123456789) THEN or something similar or more elegant, as this is getting messy too.... :(
IamIC over 13 years

Brilliant! Very obvious now that I see it.
IamIC over 13 years

I've added this the booklet I'm compiling on things to know about SQL Server. :)
Dawn over 13 years

Fab, I'm new to this site, so I'm glad that you're the first person I've helped. :) :) Do let me know when the booklet is finished. There's huge gaps in my knowledge....!
OzrenTkalcecKrznaric almost 9 years

This is really a great approach. It actually LOOKS good in my code. Reads something like "when matched and a change exists in these fields of source vs target data"
yzorg over 6 years

EXCEPT and INTERSECT documentation: docs.microsoft.com/en-us/sql/t-sql/language-elements/…
Rod Talingting about 2 years

This worked until the nullable column is a uniqueidentifier