update with case and some nulls

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

update with case and some nulls

Author

Topic

marcodif
Starting Member

2 Posts

Posted - 2012-02-10 : 15:00:50

I can't for the life of me figure out why this isn't working. I want to correct some values, change nulls to 'unknown' and uppercase all remaining values (in case they aren't uppercase). The following case doesn't work, although if I did just an update statement for only nulls to unknown it would work (update Cable set COName = 'unknown' where COName is null -- works)

UPDATE Cable
SET COName =
CASE COName
WHEN 'STTN' THEN 'STOT'
WHEN NULL THEN 'unknown'
ELSE UPPER (COName)
END

marcodif
Starting Member

2 Posts

Posted - 2012-02-10 : 15:22:47

ok, this is awkward, but I did some more digging, and found something on another forum ( http://www.sqlservercentral.com/Forums/Topic445488-145-1.aspx ), and I changed my statement slightly and it's working now:

UPDATE Cable
SET COName =
CASE
WHEN COName = 'STTN' THEN 'STOT'
WHEN COName is null THEN 'unknown'
ELSE UPPER (COName)
END