T-SQL：在 UPDATE 语句中使用 CASE 根据条件更新某些列

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

提问by pqsk

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

我想知道这是否可能。如果条件为真，我想更新列 x，否则将更新列 y

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.

我到处搜索，尝试了一些东西，但无法找到解决方案。我认为这是不可能的，但我想我会在这里问，看看是否有人以前做过。提前致谢。

回答by Adam Robinson

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 probablywon't cause you any problems, but if you have a high transactional volume, then this could cause concurrency issues.

这在语义上是相同的，但请记住，两列都将始终更新。这可能不会给您带来任何问题，但是如果您的事务量很大，那么这可能会导致并发问题。

The only way to do specificallywhat 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.

具体执行您所要求的唯一方法是使用动态 SQL。然而，这是我鼓励你远离的事情。上面的解决方案几乎肯定足以满足您的需求。

回答by Quassnoi

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

回答by Debendra Dash

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

我想使用 Case 语句将我的 ContactNo 更改或更新为 8018070999，其中有 8018070777

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

回答by Victor Eduardo Salazar Ramirez

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

问候

回答by Harsh

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.

现在这还有另一个好处，它没有像上述解决方案那样在表上进行不必要的写入。

回答by John Greiner

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)

我相信您可以通过如下调整其他答案来省略更新“非期望”列：
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

看完所有评论后，这是最有效的：
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

示例表：
CREATE TABLE [dbo].[tblTest]( [ColX] [int] NULL, [ColY] [int] NULL, [ColConditional] [bit] NULL, [id] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY]
示例数据：
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)

现在我假设您可以编写一个处理空值的条件。对于我的示例，我假设您编写了这样一个评估为 True、False 或 Null 的条件。如果您需要这方面的帮助，请告诉我，我会尽力而为。

现在运行这两行代码确实当且仅当 ColConditional 为 True(1) 时将 X 更改为 25，当且仅当 ColConditional 为 False(0) 时将 Y 更改为 25

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.

PS 原始问题或问题的任何更新中从未提到过空情况，但正如您所见，这个非常简单的答案无论如何都会处理它们。