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