CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012sql-update
pradeep kumar alugurthi
pradeep kumar alugurthi

Reputation: 101

updating multiple columns using case statement in sql server

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

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

Upvotes: 8

Views: 72065

Answers (4)

Gamzosh
Gamzosh

Reputation: 153

Note: DO NOT USE SWITCH CASE ON update statements.

Write a single query each time you want to update. If you want to automate the process of writing the query, use an excel sheet to concat expression and create queries and run them.

Switch cases work best on SELECT statements.

Upvotes: -1

Kishore Kumar
Kishore Kumar

Reputation: 78

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.

Upvotes: 0

Saravana Kumar
Saravana Kumar

Reputation: 3729

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

Upvotes: 0

Spock
Spock

Reputation: 4910

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

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

Hope that helps

Upvotes: 11

Related Questions