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.

Assuming all in the same table, yes. You could always run it in a transaction, & rollback on error, to see for yourself. — OMG Ponies, Jan 28 '11 at 15:54
I'm not sure what you mean. I have tried putting a conditional for the column, but it does not work. It works for a select statement, but not for an update statement.(select (case (condition) when true then columnx else columny end) from myTable....the update does not work, and I can see why. There doesn't seem a way to make that work. — pqsk, Jan 28 '11 at 16:16

score 210 · Accepted Answer · edited Jan 28 '11 at 16:21

210

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.

edited Jan 28 '11 at 16:21

Quassnoi

413,100
91
616
614

answered Jan 28 '11 at 15:55

Adam Robinson

182,639
35
285
343

I agree about the dynamic SQL. So will my data be affected though? I mean I don't want it changed for certain condition. So it will just reinsert what's already in there? The amount of hits to the db might not be so bad. – pqsk Jan 28 '11 at 16:12
@pqsk: This should not affect your data, it should just reinsert what's already there for whichever column is *not* supposed to be affected. – Adam Robinson Jan 28 '11 at 16:16
Thanks. I am going to go with this. So simple, even a caveman can do it. haha. – pqsk Jan 28 '11 at 16:19
1

@AdamRobinson 1.5 years are passed do you know some more efficient way to update only one column – Jun 16 '12 at 15:19
@Somebodyisintrouble: The only way to update one column is to use a different query. – Adam Robinson Jun 16 '12 at 18:26
yes what Adam has pointed out is correct, we should not use the CASE statement in the SET for update in case of large data sets with huge volumes od data. tested it and found the query extremenly slow for a single row. upvoting for pointing the drawback – vikeng21 Apr 02 '14 at 09:36

score 28 · Answer 2 · answered Jan 28 '11 at 15:55

28

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

answered Jan 28 '11 at 15:55

Quassnoi

413,100
91
616
614

1

Did you just copy Adam's response, or was this taken from somewhere else? haha. Just noticed that. – pqsk Jan 28 '11 at 16:14
1

@pqsk: Our responses were ~1 minute apart, so I'd imagine I just clicked submit a little faster ;) – Adam Robinson Jan 28 '11 at 16:15
27

@pqsk: yes, I just copied Adam's response, `23` seconds before he posted it. I'm one fast copypaster! – Quassnoi Jan 28 '11 at 16:17
2

@pqsk: if you put the cursor over the `* min ago`, it will show you exact time it was posted. – Quassnoi Jan 28 '11 at 16:20
2

To be fair, even though both are the same: if Adam's would have come out after yours, he did elaborate some more. That's why I marked his as the answer. Thanks though. – pqsk Jan 28 '11 at 16:26
@Quassnoi do you know some more efficient way to update only one column – Jun 16 '12 at 15:19
@Somebodyisintrouble: more efficient than what? – Quassnoi Jun 16 '12 at 20:23
Since your code is updating always two column but only one column is getting updated therefore is here no way to update only one column – Jun 17 '12 at 04:26

score 16 · Answer 3 · edited Jun 03 '22 at 05:26

16

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;

edited Jun 03 '22 at 05:26

cottontail

10,268
18
50
51

answered Jul 01 '19 at 13:37

Victor Eduardo Salazar Ramirez

161
1
2

2

Beware : the fields which are not respecting any condition will be set to `null` with this query. Don't forget to set a `ELSE FIELD1` after the `WHEN` cases to not touch the rest of the data. – Glioburd Apr 07 '23 at 15:51

score 4 · Answer 4 · answered Sep 26 '16 at 12:30

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)

answered Sep 26 '16 at 12:30

Debendra Dash

5,334
46
38

2

for this why not use this query UPDATE [Contacts] SET contactNo = 8018070999 WHERE contactNo = 8018070777 – NewGuy Jun 13 '17 at 15:55

score 4 · Answer 5 · answered Dec 13 '18 at 01:19

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.

this is a good point and good alternative! I'm no longer working the original code that lead to this thread, but t's always good to have different solutions and I think this is a good solution — pqsk, Feb 11 '19 at 03:17

score 0 · Answer 6 · edited Jan 03 '21 at 08:57

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.

This actually doesn't work. For one, if the column allows nulls, then when the condition is not met a null value is assigned. In the case when nulls are not allowed, then the update will fail. Your final "efficient" query is invalid sql, at least in TSQL. Did you test this on a specific engine and it worked for you? — pqsk, Feb 24 '15 at 14:07
I tested this on SQL Server 2005 and it works perfectly as shown. I would sure like to know why it was down voted and an example showing the NULL value getting updated, because in my test above, the null value is not updated. I've always thought that the simplest answer is the best and if I am dealing with a database with millions of records I sure don't want to go updating un-necessary rows. — John Greiner, Dec 10 '15 at 00:05

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

6 Answers6

Linked