Using case statement in update query

Question

Is it possible to use case statement within an update query? I need to do something like this: If person name starts with 'S' then append '1', else append '2'.

I tried this in sql server and it didn't work

UPDATE PERSON
CASE
WHEN NAME LIKE 'S%' THEN SET NAME = NAME + '1'
ELSE SET NAME = NAME + '2'
END

Answer 1

Just to add a slightly different variant that I tend to prefer (down to personal preference).

UPDATE Person
SET Name = Name + CASE WHEN Name LIKE 'S%' THEN '1' ELSE '2' END

I like this because it saves repeating the "Name +" bit for each condition - in this case it's nothing major, but in other scenarios with more conditions it can be overly repetitive

Answer 2

CASE WHEN returns an expression, not a statement. You can use it like this:

UPDATE PERSON
SET NAME = CASE WHEN NAME LIKE 'S%' THEN NAME + '1'
                                    ELSE NAME + '2'
           END

Answer 3

It will be:

update person
set name = case when name left(name,1) = 'S' then name + '1' else name + '2' end