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

Question

I am wondering if this is possible at all. I want to update column x if a condition is true, otherwise column y would be updated

UPDATE table SET
     (CASE (CONDITION) WHEN TRUE THEN columnx
                       ELSE columny
      END)
= 25

I have searched all over, tried out some things and am unable to find a solution. I think it's not possible, but I thought I would ask here and see if anyone has done it before. Thanks in advance.

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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)

Answer 5

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.

Answer 6

I believe that you can omit updating the "non-desired" columns by adjusting the other answers as follows:

update table set
    columnx = (case when condition1 then 25 end),
    columny = (case when condition2 then 25 end)`

As I understand it, this will update only when the condition is met.

After reading all the comments, this is the most efficient:

Update table set ColumnX = 25 where Condition1
 Update table set ColumnY = 25 where Condition1`

Sample Table:

CREATE TABLE [dbo].[tblTest](
    [ColX] [int] NULL,
    [ColY] [int] NULL,
    [ColConditional] [bit] NULL,
    [id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

Sample Data:

Insert into tblTest (ColX, ColY, ColConditional) values (null, null, 0)
Insert into tblTest (ColX, ColY, ColConditional) values (null, null, 0)
Insert into tblTest (ColX, ColY, ColConditional) values (null, null, 1)
Insert into tblTest (ColX, ColY, ColConditional) values (null, null, 1)
Insert into tblTest (ColX, ColY, ColConditional) values (1, null, null)
Insert into tblTest (ColX, ColY, ColConditional) values (2, null, null)
Insert into tblTest (ColX, ColY, ColConditional) values (null, 1, null)
Insert into tblTest (ColX, ColY, ColConditional) values (null, 2, null)

Now I assume you can write a conditional that handles nulls. For my example, I am assuming you have written such a conditional that evaluates to True, False or Null. If you need help with this, let me know and I will do my best.

Now running these two lines of code does infact change X to 25 if and only if ColConditional is True(1) and Y to 25 if and only if ColConditional is False(0)

Update tblTest set ColX = 25 where ColConditional = 1
Update tblTest set ColY = 25 where ColConditional = 0

P.S. The null case was never mentioned in the original question or any updates to the question, but as you can see, this very simple answer handles them anyway.