SQL 2K NULL Value Question

• loopman

  SSC-Addicted

  Points: 468

  More actions

  December 30, 2008 at 10:42 am

  #197626

  Hi,

  This seems like a simple question but I am so annoyed that the following

  SQL statement doesn't return any values. I have a table with a column call userRole with *tinyint". Currently there is no data as they are all NULL values. I wanted to update the column with a number as below. But this doesn't work... it says 0 rows affected I read the update articles and it seems NULL doesn't return TRUE. But then how do I update a column with already in NULL values. Thanks for an answer..

  UPDATE users

  SET userRole='3'

  WHERE userRole = NULL

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  December 30, 2008 at 10:45 am

  #919152

  NULL is not a value, so comparing anything to it will fail. (NULL is a state)

  Meaning A=Null will never be true, and A<>NULL will also never be true.

  You need to use IS NULL or IS NOT NULL.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• loopman

  SSC-Addicted

  Points: 468

  More actions

  December 30, 2008 at 10:49 am

  #919154

  I love this site! Thank you and have a happy newyear!!