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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-update

Examples related to case