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 · Accepted 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

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

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  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

I believe that you can omit updating the  quot non-desired quot  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

[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