updating multiple columns using case statement in sql server
51,108
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
Author by
pradeep kumar alugurthi
Updated on January 12, 2021Comments
-
pradeep kumar alugurthi over 3 years
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