CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Prabhu
Prabhu

Reputation: 3528

Using case statement in update query

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

Upvotes: 8

Views: 33440

Answers (3)

Antony Koch
Antony Koch

Reputation: 2053

It will be:

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

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147224

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

Upvotes: 10

Heinzi
Heinzi

Reputation: 172220

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

Upvotes: 7

Related Questions