Set multiple fields in a single case statement

Question

I'm not as familiar with SQL Server 2008 and I haven't seen an answer to this question anywhere else. Does TSQL in SQL Server 2008 allow for multiple fields to be set in a single case statement. For instance:

case MyField
    when 1 then ThisField = 'foo', ThatField = 'bar'
    when 2 then ThisField = 'Mickey', ThatField = 'Mouse'
    else ThisField = NULL, ThatField = NULL
end

Since my condition doesn't change, it would be nice to set all fields based on that condition instead of using multiple case statements with a duplicated condition.

Answer 1

No, case only returns a scalar value.

ThisField = case MyField when 1 then 'foo' when 2 then 'Mickey' end,
ThatField = case MyField when 1 then 'bar' when 2 then 'Mouse' end 

Answer 2

When you are using CASE, you can only use that to get an output value, not to assign a value. You can write multiple update queries with WHERE clause to get what you intend to update.

Just saw, the answer from RedFilter seems to be way to update in one single query.

Answer 3

You could use a JOIN onto a derived table or CTE

;WITH updates AS
(
SELECT 1 AS MyField, 'foo' AS ThisField, 'bar' AS ThatField UNION ALL
SELECT 2 AS MyField, 'Mickey' AS ThisField, 'Mouse' AS ThatField 
)
UPDATE    YourTable
SET              ThisField =updates.ThisField, ThatField=updates.ThatField
FROM         YourTable LEFT OUTER JOIN
                      updates ON YourTable.MyField = t.MyField

Or for a SELECT

;WITH mappings AS
(
SELECT 1 AS MyField, 'foo' AS ThisField, 'bar' AS ThatField UNION ALL
SELECT 2 AS MyField, 'Mickey' AS ThisField, 'Mouse' AS ThatField 
)
SELECT YourTable.MyField,  
       mappings.ThisField,
       mappings.ThatField
FROM         YourTable LEFT OUTER JOIN
                      mappings ON YourTable.MyField = t.MyField