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

wz3gfoph 于 2023-08-02 发布在其他

关注(0)|答案(9)|浏览(100)

In SQL Server with a MERGE code, everything is fine except when there are 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). 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';

sql-server

来源：https://stackoverflow.com/questions/4509722/nulls-and-the-merge-statement-i-need-to-set-a-value-to-infinity-how

9条答案

按热度按时间

polhcujo1#

In SQL Server 2022 you can use

WHEN MATCHED AND tgt.C IS DISTINCT FROM src.C

In previous versions you can use

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

The second version can still be more compact if you need to do this check across multiple columns.

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

See this article for more on this issue.

赞(0）回复(0）举报 2023-08-02

z9smfwbn2#

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

赞(0）回复(0）举报 2023-08-02

nhaq1z213#

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

赞(0）回复(0）举报 2023-08-02

eni9jsuy4#

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

赞(0）回复(0）举报 2023-08-02

ajsxfq5m5#

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

赞(0）回复(0）举报 2023-08-02

rm5edbpk6#

This works as well and may be better when you have multiple columns that you want to check if they are different.

MERGE @t2 a

  using @t1 b

  ON a.PK = b.PK

  WHEN MATCHED AND CHECKSUM(a.PK,a.VALUE)!= CHECKSUM(b.pk,b.VALUE)

  THEN UPDATE SET a.VALUE = b.VALUE;

赞(0）回复(0）举报 2023-08-02

czq61nw17#

You can check for null in the ON Clause:

MERGE TargetTable
USING (VALUES (0)) as s(x)
ON last_run is not null
WHEN not matched then
insert (last_run) values(getdate())
when matched then
update set last_run=getDate();

赞(0）回复(0）举报 2023-08-02

wrrgggsh8#

Instead of using 0 when the values are null, why not use a value that is highly unlikely to exist? EG (IsNull(tgt.C, 2093128301).

The datatypes are int so you have a lot to play with......

赞(0）回复(0）举报 2023-08-02

6tdlim6h9#

WHEN MATCHED AND
(
   NULLIF(tgt.C, src.C) IS NOT NULL OR NULLIF(src.C, tgt.C) IS NOT NULL
)
THEN

赞(0）回复(0）举报 2023-08-02

我来回答

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

9条答案

相关问题

热门标签

最新问答