Selective updates to NULL

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

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

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