T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition

Question

You can't use a condition to change the structure of your query, just the data involved. You could do this:

update table set
    columnx = (case when condition then 25 else columnx end),
    columny = (case when condition then columny else 25 end)

This is semantically the same, but just bear in mind that both columns will always be updated. This probably won't cause you any problems, but if you have a high transactional volume, then this could cause concurrency issues.

The only way to do specifically what you're asking is to use dynamic SQL. This is, however, something I'd encourage you to stay away from. The solution above will almost certainly be sufficient for what you're after.

UPDATE  table
SET     columnx = CASE WHEN condition THEN 25 ELSE columnx END,
        columny = CASE WHEN condition THEN columny ELSE 25 END

I know this is a very old question, but this worked for me:

UPDATE TABLE SET FIELD1 =
CASE 
WHEN FIELD1 = Condition1 THEN 'Result1'
WHEN FIELD1 = Condition2 THEN 'Result2'
WHEN FIELD1 = Condition3 THEN 'Result3'
END;

Regards

I want to change or update my ContactNo to 8018070999 where there is 8018070777 using Case statement

update [Contacts] set contactNo=(case 
when contactNo=8018070777 then 8018070999
else
contactNo
end)

I know this is a very old question and the problem is marked as fixed. However, if someone with a case like mine where the table have trigger for data logging on update events, this will cause problem. Both the columns will get the update and log will make useless entries. The way I did

IF (CONDITION) IS TRUE
BEGIN
    UPDATE table SET columnx = 25
END
ELSE
BEGIN
    UPDATE table SET columny = 25
END

Now this have another benefit that it does not have unnecessary writes on the table like the above solutions.