Why is the "is null" throwing a syntax error?:
UPDATE [GCDF_DB].[dbo].[People]
SET [educationtext] =
CASE [education]when 1 then 'Post Graduate'
when 2 then '4 Year Degree'
when 3 then 'Associates Degree'
when 4 then 'High School'
when is null then 'Unknown'END
You can't use IS NULL in a CASE expression using the CASE {value} WHEN {expression} format; you must use the CASE WHEN {value} = {expression} style syntax:
USE GCDF_DB;
UPDATE [dbo].[People]
SET [educationtext] = CASE WHEN [education] = 1 THEN 'Post Graduate'
WHEN [education] = 2 THEN '4 Year Degree'
WHEN [education] = 3 THEN 'Associates Degree'
WHEN [education] = 4 THEN 'High School'
WHEN [education] IS NULL THEN 'Unknown'
END;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 6, 2020 at 4:36 pm
Presumably because NULL is not a value.
Have you considered just using ELSE?
Otherwise, use the extended 'Searched CASE' syntax:
... WHEN Education is NULL THEN 'Unknown'
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
January 7, 2020 at 5:08 pm
UPDATE [GCDF_DB].[dbo].[People]
SET [educationtext] =
CASE [education]when 1 then 'Post Graduate'
when 2 then '4 Year Degree'
when 3 then 'Associates Degree'
when 4 then 'High School'
ELSE 'Unknown'END
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply