Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pats2kDynasty
Starting Member
16 Posts |
 Posted - 2009-06-05 : 16:00:17
|
| I run this query:UPDATE tblX SET intFieldA = intFieldA2 from viewYWHERE intFieldB = intFieldB2 AND intFieldA != intFieldA2 Weird part is that when intFieldA equals NULL it will not update the field. When I manual change intFieldA to some random number the above query does update the field. When I change intFieldA back to NULL it will not update.Has anyone ever seen this? If so, what is the solution?Thanx in advance! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-06-05 : 16:06:42
|
| with the default setting of ANSI_NULLS a NULL value will never = anything. That is why you have to say <somefield> IS null rather than = null. So the solution is to use isNull or Coalesce when JOINing on nullable columns.so perhaps this will work:WHERE isNull(intFieldB,-1) = isNull(intFieldB2,-2) AND isNull(intFieldA,-1) != isNull(intFieldA2, -1)EDIT:I've mixed in JOINing on nullable columns and FILTERing on Nullable columns but the same concept applies to bothBe One with the OptimizerTG |
 |
|
|
pats2kDynasty
Starting Member
16 Posts |
Posted - 2009-06-05 : 16:54:13
|
| wow...never would of thought of that. THANK YOU! Is this still true in 2005 and 2008? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-06-05 : 16:57:55
|
| >>Is this still true in 2005 and 2008?Yes, it is.I should clarify that when you want to SET a value to null you should use: set @value=nullbut to compare you need to: where @value IS nullEDIT:declare @v intset @v = 10set @v = null --To set the value use "="select @v where @v = nullselect @v where @v is null --To compare the value use "is"Be One with the OptimizerTG |
|
|
|
|
|
|
This is weird?!?