Post reply

Update where field is NULL

  • July 13, 2009 at 10:22 am

    #223585

    I am trying to run the following script.

    UPDATE AR_CUST

    SET PROF_COD_4 = 'Y'

    WHERE PROF_DAT_3 <> NULL

    Why does it say "0 row(s) affected"? It should be returning results. What am I doing wrong?

  • July 13, 2009 at 10:27 am

    #1024027

    On most systems, you cannot say WHERE something NULL... instead, you usually have to say WHERE something IS NOT NULL or WHERE something IS NULL depending on what you're trying to do.

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

  • July 13, 2009 at 10:40 am

    #1024036

    Worked perfectly. Thank you so much!

  • July 14, 2009 at 5:30 am

    #1024327

    You bet. Thank you for the feedback.

    As a bit of a sidebar, NULLs are some of the oddest things and can also be a very, very powerful tool depending on what you're trying to do. For example, if you want to find things in a VARCHAR column that are neither NULL nor BLANK, you don't need to test for both conditions. You merely need to provide a WHERE somecol > ' ' . Because you cannot use relational operators against most databases, NULLs will also be excluded by that WHERE clause.

    Also, adding or concatenating NULL to any value or string will result in NULL. That can be both a very powerful tool and a very powerful pain.

    It only takes a bit of study to master the essentials of NULLs and their sometimes weird effects... even if someone isn't new to SQL, it's worth revisiting about once a year as a refresher. Start by looking up "null values [SQL Server]" in Books Online. Forget anything you read about setting ANSI NULLS to off because it's been deprecated and will go away in the future.

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

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

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