Reputation: 101
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
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
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
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
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