blocks|key|244253|text|像这样的东西应该是有效的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|244254|UPDATE
++++table_Name
SET+
++column_A+=+CASE+WHEN+@flag+=+'1'+THEN+column_A+%2B+@new_value+ELSE+column_A+END,
++column_B+=+CASE+WHEN+@flag+=+'0'+THEN+column_B+%2B+@new_value+ELSE+column_B+END
WHERE
++++ID+=+@ID|code-block|syntax|javascript|244255|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Something like this should work:

<pre><code>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
</code></pre>

blocks|key|28057|text|由于您使用的是SQL+2008：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|28058|UPDATE
++++table_Name

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

++++column_B++
+++++=+CASE
++++++++WHEN+@flag+=+'0'+THEN+@new_value
++++++++ELSE+0
++++END+%2B+column_B+
WHERE
++++ID+=+@ID|code-block|syntax|javascript|28059|如果您使用的是SQL+2012：|28060|UPDATE
++++table_Name
SET
++++column_A++=+column_A+%2B+IIF(@flag+=+'1',+@new_value,+0),
++++column_B++=+column_B+%2B+IIF(@flag+=+'0',+@new_value,+0)
WHERE
++++ID+=+@ID|28061|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Since you're using SQL 2008:

<pre><code>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
</code></pre>

If you were using SQL 2012:

<pre><code>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
</code></pre>

blocks|key|244363|text|目前的答案很好，应该可以工作，但是更简单、更明显和更可维护的答案有什么问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|244364|IF+@flag+=+1
++++UPDATE+table_name+SET+column_A+=+column_A+%2B+@new_value+WHERE+ID+=+@ID;
ELSE
++++UPDATE+table_name+SET+column_B+=+column_B+%2B+@new_value+WHERE+ID+=+@ID;|code-block|syntax|javascript|244365|尽管这是一个非常简单的查询，但阅读起来要容易得多。|244366|这是一个由@snyder提供的工作示例：SqlFiddle。|offset|length|244367|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!3/74fcd/12^0|0|0|0|K|9|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

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

<pre><code>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;
</code></pre>

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

Here's a working example courtesy of @snyder: <a href="http://sqlfiddle.com/#!3/74fcd/12" rel="noreferrer">SqlFiddle</a>.

blocks|key|244387|text|这样做效果很好：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|244388|UPDATE
++++table_Name
SET+
++column_A+=+CASE+WHEN+@flag+=+'1'+THEN+column_A+%2B+@new_value+ELSE+column_A+END,
++column_B+=+CASE+WHEN+@flag+=+'0'+THEN+column_B+%2B+@new_value+ELSE+column_B+END
WHERE
++++ID+=+@ID|code-block|syntax|javascript|244389|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

this worked great:

<pre><code>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
</code></pre>

blocks|key|28131|text|DECLARE+@JCnt+int=null
SEt+@JCnt=(SELECT+COUNT(+ISNUll(EmpCode,0))+FROM+tbl_Employees+WHERE+EmpCode=1++)

UPDATE+#TempCode
SET+janCA=+CASE+WHEN+@JCnt>0+THEN+(SELECT+SUM+(ISNUll(Amount,0))+FROM+tbl_Salary+WHERE+Code=1+)ELSE+0+END
WHERE+code=1|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|28132|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DECLARE @JCnt int=null
SEt @JCnt=(SELECT COUNT( ISNUll(EmpCode,0)) FROM tbl_Employees WHERE EmpCode=1 )

UPDATE #TempCode
SET janCA= CASE WHEN @JCnt&gt;0 THEN (SELECT SUM (ISNUll(Amount,0)) FROM tbl_Salary WHERE Code=1 )ELSE 0 END
WHERE code=1
</code></pre>

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:

<pre><code>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
</code></pre>

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

if condition in sql server update query

服务器

我有一个SQL服务器表，其中有2列，我希望根据发送给存储过程的标志和新值一起更新它们的值，如下所示：UPDATE    table_NameSET    CASE        WHEN @flag = '1' THEN column_A += @new_value        WHEN @flag = '0' TH...

问sql server update查询中的if条件
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问sql server update查询中的if条件EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问sql server update查询中的if条件
EN