Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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]
share|improve this answer
    
Thanks for going above and beyond with the comprehensive answer a1ex07, really helped me out. –  David Dec 31 '12 at 16:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.