Sql-server – T-SQL Case functionality

sql-server-2008

Is it possible to structure a CASE statement as follows?

For each Path column I want to update the Value column, but the values aren't being set and I'm not sure how to proceed. The columns are all nvarchars(255).

I've taken this code from an answer on StackOverflow; however in that case the columns listed after SET and CASE were the same. I realize that there are other methods achieve the same result, but this is the least verbose I've found.

UPDATE Database.[dbo].[Table] 
SET Value = 
CASE Path
    WHEN '\Folder1' THEN '0'
    WHEN '\Folder2' THEN 'xyz'
    WHEN '\Folder3' THEN '0'
    ELSE Value 
END

Best Answer

If Value column doesn't change, then for each row only latest else of your case is true. Syntax itself is correct, and it should work as you want. I'd first do SELECT to double check that constants in CASE correspond to what you have in the table:

SELECT DISTINCT
CASE Path
WHEN '\Folder1' THEN '0'
WHEN '\Folder2' THEN 'xyz'
WHEN '\Folder3' THEN '0'
END as val
FROM Database.[dbo].[Table]

It should return up to 3 rows ('0','xyz', and NULL). If it returns 1 row only(NULL), then you don't have records where the value of Path column equals to any of '\Folder1','\Folder2','\Folder3'.

Note : the syntax of CASE you use compares value of Path column with constants using =. You might have wanted to use another logic, for instance LIKE. Then use different form of CASE :

SELECT DISTINCT
CASE 
WHEN Path LIKE '\Folder1%' OR Path LIKE '\Folder3%' THEN '0'
WHEN Path LIKE '\Folder2%' THEN 'xyz'    
END as val
FROM Database.[dbo].[Table]
Related Question