sql server更新查询中的if条件

I have a SQL server table in which there are 2 columns that I want to update either of their values according to a flag sent to the stored procedure along with the new value, something like:

我有一个SQL server表，其中有两个列，我想根据发送给存储过程的标志以及新值来更新它们的值，比如:

UPDATE
    table_Name

SET
    CASE
        WHEN @flag = '1' THEN column_A += @new_value
        WHEN @flag = '0' THEN column_B += @new_value
    END AS Total

WHERE
    ID = @ID

What is the correct SQL server code to do so??

正确的SQL服务器代码是什么?

4 个解决方案

#1

The current answers are fine and should work ok, but what's wrong with the more simple, more obvious, and more maintainable:

目前的答案很好，应该可以工作，但更简单、更明显、更易于维护的问题是:

IF @flag = 1
    UPDATE table_name SET column_A = column_A + @new_value WHERE ID = @ID;
ELSE
    UPDATE table_name SET column_B = column_B + @new_value WHERE ID = @ID;

This is much easier to read albeit this is a very simple query.

尽管这是一个非常简单的查询，但阅读起来要容易得多。

Here's a working example courtesy of @snyder: SqlFiddle.

下面是@snyder: SqlFiddle提供的工作示例。

#2

Something like this should work:

像这样的东西应该是有用的:

UPDATE
    table_Name
SET 
  column_A = CASE WHEN @flag = '1' THEN column_A + @new_value ELSE column_A END,
  column_B = CASE WHEN @flag = '0' THEN column_B + @new_value ELSE column_B END
WHERE
    ID = @ID

#3

Since you're using SQL 2008:

因为您正在使用SQL 2008:

UPDATE
    table_Name

SET
    column_A  
     = CASE
        WHEN @flag = '1' THEN @new_value
        ELSE 0
    END + column_A,

    column_B  
     = CASE
        WHEN @flag = '0' THEN @new_value
        ELSE 0
    END + column_B 
WHERE
    ID = @ID

If you were using SQL 2012:

如果您正在使用SQL 2012:

UPDATE
    table_Name
SET
    column_A  = column_A + IIF(@flag = '1', @new_value, 0),
    column_B  = column_B + IIF(@flag = '0', @new_value, 0)
WHERE
    ID = @ID

#4

this worked great:

这个伟大的工作:

UPDATE
    table_Name
SET 
  column_A = CASE WHEN @flag = '1' THEN column_A + @new_value ELSE column_A END,
  column_B = CASE WHEN @flag = '0' THEN column_B + @new_value ELSE column_B END
WHERE
    ID = @ID

#1