MERGE - Comparing NULL values for Update

Question

MERGE - Comparing NULL values for Update

mitzyturbo

Hall of Fame

Points: 3452
More actions
September 4, 2019 at 10:23 am
Go to Answer
#3677145
Hi guys
Came across an odd one today. I'm running a MERGE to do an INSERT and UPDATE between a source and target table.
The matching values are based on the PK columns, when not matched condition results in an INSERT (works) when matched AND any of the other values on the tables don't match then UPDATE (doesn't work for particular columns).
I added 4 new columns to the target table (are all NULL by default when added).
In the current scenario, I have a matching condition but the it is not picking this up as the ISNULL that I wrap around the value doesn't appear to be working:
```
MERGE
Table1 AS target 
USING 
#Table2 AS source
ON (
target.ID1 = source.ID1
and target.ID2 = source.ID2
and target.Date = source.Date
)
WHEN MATCHED AND--UPDATE EXISITING RECS WHERE CHANGED
(
--Compares each of the other values for differences, including the new columns
OR ISNULL(TARGET.[Balance],0.0) <> ISNULL(SOURCE.[Balance],0.0)
)
```
The value on the source is 0.0, the value on target is NULL but the ISNULL doesn't seem to cover the comparison as the update does not get picked up.
The only other option I can think of is to update all values to 0.0 upon creation of the columns and then add a constraint to set the default value for this column to 0.0.
Any other suggestions I can try here or why the ISNULL doesn't appear to work?
Regards
mitzyturbo

Hall of Fame

Points: 3452
More actions
September 4, 2019 at 10:34 am
Answer
#3677148

Scratch this guys, its only after posting that I see that it wouldn't update anyway as there's no difference in the values.
I'm just going to go sit in the corner and hit my head off the wall for a while 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

mitzyturbo wrote:

Scratch this guys, its only after posting that I see that it wouldn't update anyway as there's no difference in the values.
I'm just going to go sit in the corner and hit my head off the wall for a while 🙂

Heh... that and short naps get me through the day. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

NULL and 0.0 are not the same thing.

If you need the values to be 0.0, you should make sure they are INSERTed as 0. Use a default value and a trigger to insure that they are INSERTed that way.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".