SQL Server Performance Forum – Threads Archive
Probelm with Case in Query
Hello All I Have Problem with Case Statement.I Have Query like below update Table1set Col1 = Case Col2
When ‘Xyz’ then ‘A’
When ‘ABC’ then ‘X’
When ‘XXX’ then ‘C’
More then 5000 case
end when i try to execute this Query with more then 812 case at that time it will raise error. I have more then 5000 Case which i want to execute in one single Query. Please send me if there is any other Option to execute this Query. Jatin Purohit
(M)- +919426025391
You could create a table variable/temporary table to hold the translations you want and then join against that… or split the update into a number of smaller updates each with an else col1 at the end to ensure a column value is not changed if it is not covered by this update. a case statement was never really intended for a large number of cases
Cheers
Twan
As i don’t want to use join in My Query.
when i use join at that time it will not gave me good Performance.
if any other Suggestion on this which i can use my query without Use of any temp or real table. Jatin Purohit
(M)- +919426025391
the table you would create will be very narrow, very small (2 columns, 5000 rows) and if well indexed will add less overhead than a case statement would. I’d use a second table. It also will reduce the chance of typing errors in 5000 lines of case statements, because you can query the new table to make sure its got what you want, no values are missed and none are duplicated (that you dont want duplicated). Just have an index on both of your tables for the col2 field and it will run very, very fast. Put a clustered index on your lookup table. A non-clustered on your main table will be fine. There is no real other option to do what you want, other than a cursor with 5000 if statements, which would be slower than anything you could write in a join.
]]>