Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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';
like image 595
IamIC Avatar asked Dec 22 '10 13:12

IamIC


People also ask

How do I MERGE NULL values in SQL?

Since it's not possible to join on NULL values in SQL Server like you might expect, we need to be creative to achieve the results we want. One option is to make our AccountType column NOT NULL and set some other default value. Another option is to create a new column that will act as a surrogate key to join on instead.

How do you set a value to NULL?

If you've opened a table and you want to clear an existing value to NULL, click on the value, and press Ctrl + 0 . Show activity on this post.

How do you add a value to a NULL column?

You also can specify the NULL keyword in the VALUES clause to indicate that a column should be assigned a NULL value. The following example inserts values into three columns of the orders table: INSERT INTO orders (orders_num, order_date, customer_num) VALUES (0, NULL, 123);

How do you convert NULLs to zero?

Use IFNULL or COALESCE() function in order to convert MySQL NULL to 0. Insert some records in the table using insert command. Display all records from the table using select statement.


2 Answers

You can use

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

See this article for more on this issue.

like image 83
Martin Smith Avatar answered Sep 28 '22 04:09

Martin Smith


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 -- ...
like image 25
Anthony K Avatar answered Sep 28 '22 03:09

Anthony K