在sql server中使用case语句更新多列 [英] updating multiple columns using case statement in sql server

问题描述

我想使用 case 语句更新表，查询是这样的...

I would like to update the table using case statement the query is like this...

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

能否请您说出正确的逻辑来使用 case 语句完成查询

can u please tell the correct logic to complete the query using case statement

推荐答案

您必须交换语法.case 语句将应用于您要更新的每个值...

You'll have to swap the syntax around. The case statement will be applied for every value you want to update...

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

看起来你真正想要的是一个 if 语句......

It looks like you actually want is a if statement....

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

希望有帮助

这篇关于在sql server中使用case语句更新多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！