Selective updates to NULL

Question

Post reply

Selective updates to NULL

Phil Parkin

SSC Guru

Points: 246963
More actions
August 19, 2016 at 6:34 am

#329476

OK, some Friday fun.
Here's a set-up script:
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL
DROP TABLE #test;
CREATE TABLE #test
(
Id INT PRIMARY KEY
,Flag BIT NULL
);
INSERT #test
(Id, Flag)
VALUES (1, 0),
(2, NULL);
SELECT *
FROM #test t;
Now, let's assume I want to build some code to update the Flag column. I might start with something like this:
DECLARE @Id INT = 1;
DECLARE @Flag BIT = NULL;
UPDATE #test
SET Flag = @Flag
WHERE Id = @Id;
SELECT *
FROM #test t;
Does the job, right?
But, being a thoughtful developer, I want to perform the update only if the value of Flag is being changed. The fact that Flag is nullable (and that it might have a value which I want to set back to NULL), suggests that I have to write code like this (note that as SET ANSI_NULLS OFF is deprecated, I don't want to use that).
DECLARE @Id INT = 2;
DECLARE @Flag BIT = NULL;
UPDATE #test
SET Flag = @Flag
WHERE Id = @Id
AND (
Flag <> @Flag
OR (
Flag IS NULL
AND @Flag IS NOT NULL
)
OR (
Flag IS NOT NULL
AND @Flag IS NULL
)
);
I know that this could be modeled differently using, for example, a non-nullable Tinyint having (-1,0,1) as possible values, but is there a tidier solution for the nullable Bit problem described above?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 10 posts - 1 through 9 (of 9 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

I have a couple ideas I'll play around with when I'm at a PC shortly.

non-nullable Tinyint having (-1,0,1) as possible values

tinyint can't be negative 😉

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

drew.allen SSC Guru Points: 76987 More actions · Answer 2

I like the following method, particularly when you are comparing multiple columns.

UPDATE #test

SET Flag = @Flag

CROSS APPLY (

SELECT Flag

EXCEPT

SELECT @Flag

) AS diff

WHERE Id = @Id

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Why can't you use this, Phil (substituting "peanut" for an appropriate integer value):

UPDATE #test

SET Flag = @Flag

WHERE Id = @Id

AND ISNULL(Flag,peanut) <> ISNULL(@Flag,peanut)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

drew.allen SSC Guru Points: 76987 More actions · Answer 4

ChrisM@Work (8/19/2016)
Why can't you use this, Phil (substituting "peanut" for an appropriate integer value):
UPDATE #test
SET Flag = @Flag
WHERE Id = @Id
AND ISNULL(Flag,peanut) <> ISNULL(@Flag,peanut)

Because that won't update the row when Flag is null and @Flag is "peanut" or when Flag is "peanut" and @Flag is null.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Or even this:

UPDATE #test

SET Flag = CASE

WHEN ISNULL(Flag,peanut) <> ISNULL(@Flag,peanut) THEN @Flag

ELSE Flag -- <-- NON-UPDATING UPDATE

END

WHERE Id = @Id

Apparently, updating a value to itself doesn't actually do what you expect. Reference forthcoming...

Edit: Reference

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

drew.allen (8/19/2016)
ChrisM@Work (8/19/2016)
Why can't you use this, Phil (substituting "peanut" for an appropriate integer value):
UPDATE #test
SET Flag = @Flag
WHERE Id = @Id
AND ISNULL(Flag,peanut) <> ISNULL(@Flag,peanut)
Because that won't update the row when Flag is null and @Flag is "peanut" or when Flag is "peanut" and @Flag is null.
Drew

Friday afternoon after a decent lunch. Thanks Drew.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 246963 More actions · Answer 7

drew.allen (8/19/2016)
I like the following method, particularly when you are comparing multiple columns.
UPDATE #test
SET Flag = @Flag
CROSS APPLY (
SELECT Flag
EXCEPT
SELECT @Flag
) AS diff
WHERE Id = @Id
Drew

Thanks for the EXCEPT reminder. I quite like this version:

UPDATE #test

SET Flag = @Flag

WHERE Id = @Id

AND EXISTS (SELECT flag EXCEPT SELECT @Flag);

Chris, holy ****, I now remember that thread which you referenced! Fun to watch Sergiy and Gail duelling.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76987 More actions · Answer 8

This will also work.

UPDATE #test

SET Flag = @Flag

WHERE Id = @Id

AND COALESCE(Flag, ~@Flag, 0) <> COALESCE(@Flag, ~Flag, 0)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246963 More actions · Answer 9

Alan.B (8/19/2016)
I have a couple ideas I'll play around with when I'm at a PC shortly.
non-nullable Tinyint having (-1,0,1) as possible values
tinyint can't be negative 😉

Oops! You can tell how far I went down that road. Thanks, Alan.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin