SQL Update query

Question

Can i use several WHEN conditions in UPDATE clause to update a single column.

I want to update table TABLE having columns ID and NAME:

Is below query correct?

UPDATE TABLE 
   SET id = CASE id
              WHEN id IN (2, 3, 4) THEN 1
              WHEN id= 5 THEN 8
              WHEN id IN(9, 7) THEN 6
 WHERE name = 'abc'

Answer 1

Yes, that is allowed, but remove ID after CASE. And, you need to END your case.

UPDATE TABLE 
SET ID = CASE  
    when ID in (2,3,4) 
        then 1 
    when ID = 5 
        then 8 
    when ID in (9,7) 
        then 6 
END
where NAME = 'abc' 

There are two alternate syntaxes for CASE. As above, and the other is where you want to compare a single value against others, like this:

UPDATE TABLE 
SET ID = CASE ID 
    when 2
        then 1 
    when 5 
        then 8 
    when 7
        then 6 
END
where NAME = 'abc' 

Answer 2

Case comes in two versions:

version 1:

 Case Id
     When 2 Then 1
     When 3 Then 1
     When 4 Then 1
     When 5 Then 8
     When 7 Then 6
     When 9 Then 6
     End

version 2:

 Case    
   When Id in (2,3,4) Then 1   
   When Id = 5        Then 8   
   When Id in (9,7)   Then 6   
 End

Both of above are equivilent