SQL 在sql server中使用case语句更新多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27716671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
updating multiple columns using case statement in sql server
提问by pradeep kumar alugurthi
I would like to update the table using case statement the query is like this...
我想使用 case 语句更新表，查询是这样的......
select case(@columnname) when 'name1' then
begin
update table
set
pay1=pay1* 100
pay2=pay2*20
pay3=pay3* 100
end
when 'name2' then
begin
update table
set
pay2=pay2*20
pay3=pay3* 100
end
when 'name3' then
begin
update table
set
pay3=pay3* 100
end
end
can u please tell the correct logic to complete the query using case statement
你能告诉正确的逻辑来使用case语句完成查询吗
采纳答案by Spock
You'll have to swap the syntax around. The case statement will be applied for every value you want to update...
您将不得不交换语法。case 语句将应用于您要更新的每个值...
UPDATE table SET
pay1 = CASE WHEN @columnname IN('name1') THEN pay1 * 100 ELSE pay1 END,
pay2 = CASE WHEN @columnname IN('name1', 'name2') THEN pay2 * 20 ELSE pay2 END,
pay3 = CASE WHEN @columnname IN('name1', 'name2', 'name3') THEN pay3 * 100 ELSE pay3 END
It looks like you actually want is a if statement....
看起来你真正想要的是一个 if 语句......
IF @columnname = 'name1'
UPDATE table SET pay1 = pay1 * 100, pay2=pay2*20, pay3=pay3* 100
ELSE IF @ColumnName = 'name2'
UPDATE table SET pay2 = pay2 * 20, pay3 = pay3 * 100
ELSE IF @ColumnName = 'name3'
UPDATE table SET pay3 = pay3 * 100
Hope that helps
希望有帮助
回答by Saravana Kumar
Use this.
用这个。
update table
set pay1 = CASE WHEN @columnname = 'name1'
THEN pay1* 100
ELSE pay1
set pay2 = CASE WHEN @columnname = 'name1'
OR @columnname = 'name2'
THEN pay2* 20
ELSE pay2
set pay3 = CASE WHEN @columnname = 'name1' OR
@columnname = 'name2' OR
@columnname = 'name3'
THEN pay3 * 100
ELSE pay3
回答by Kishore Kumar
You can use something like this :
你可以使用这样的东西：
UPDATE DBO.Test1
SET val1=
CASE name
WHEN 'name1' THEN val1*100
ELSE val1
END,
val2=
CASE name
WHEN 'name1' THEN val2*20
WHEN 'name2' THEN val2*20
ELSE val2
END,
val3=
CASE name
WHEN 'name1' THEN val3*100
WHEN 'name2' THEN val3*100
WHEN 'name3' THEN val3*100
ELSE val3
END;
I have used different 'When' clauses for updating val3. You can use single 'WHEN' for updating val3 since the value to be updated is same in all cases.
我使用了不同的“When”子句来更新 val3。您可以使用单个“WHEN”来更新 val3，因为要更新的值在所有情况下都相同。
