How to write" else if " statement in update clause in SQL(Sqlserver 2008)

kravindra used Ask the Experts™
Is't possible to check the condition using "IF else or else if" statement in set block of update command in SQL?

I know the case statement to check the conditions in set block of update clause. i want to know is't possible to write IF statements to check the conditions in set block of update clause. if it possible please send  the examples.

for example the table name product. it has columns like  route,product group, state. i have to update the route columns based on  the productnumber and state column values.

If productNumber 12 or 44 then route to 'A'

· If productNumber 04 or 16 and

         state is ‘HYD’, ‘MP’, then route to 'B'

· If  state is 'TPT' then route to 'C'

        except nursing - 'D'
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
maybe this will help.  

Update [table]
Set    route = CASE When productNumber = 12 OR productNumber = 44
                      Then 'A'
                    When (productNumber = 4 OR productNumber = 16) and (state = 'HYD' and state = 'MP')
                      Then 'B'
                    When state = 'TPT' 
                      Then 'C'
                    Else 'D'

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

No chance. You can only use CASE, as shown above, to implement conditional values. And it can only return a single value per row. Inside of an UPDATE you need expressions - statements are not allowed.

The usual way to process complex conditions is to create a temporary or in-memory table, filling it with IF and whatever is needed statements, and then use that as reference tabe for updates.
KinjalKumar PatelSenior Software Engineer

There is no other way than using the CASE Statement to accomplish what you are expecting. And actually CASE Can help you to write the nested IF-ELSEIF-ELSE Statements also.

CASE is similar to a SWITCH Statement what we had in our programming languages. Otherwise rotate the cursors and check all values one by one and try to accomplish the same.

Hope this helps.

Let me know in case if you need more clarification.

lcohanDatabase Analyst

You could put complex code in SQL UDF functions(scalar) then run a single UPDATE statement on a table and JOIN with that scalar function however this could be a performance issue if you have large tables with many rows to update.


Good job.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial