T-SQL:在 UPDATE 语句中使用 CASE 根据条件更新某些列 [英] T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition

问题描述

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

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.

推荐答案

您不能使用条件来更改查询的结构，只能更改所涉及的数据.你可以这样做:

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.

具体您要求的唯一方法是使用动态 SQL.但是，我建议您远离这种情况.上面的解决方案几乎肯定足以满足您的需求.

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.

这篇关于T-SQL:在 UPDATE 语句中使用 CASE 根据条件更新某些列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！