MYSQL multiple updates

Question

I want to update the same column of a table, for different cases in one shot. What is the best way I can achieve this? For example, I want to achieve the same thing as the below statements in one SQL query :

UPDATE DANCER_TYPE SET TYPE = 'BALLET' WHERE LOGIN = 'arachel';
UPDATE DANCER_TYPE SET TYPE = 'EXOTIC_BELLY_DANCER' WHERE LOGIN = 'mandanah';
UPDATE DANCER_TYPE SET TYPE = 'JAZZ' WHERE LOGIN = 'kbianca';
UPDATE DANCER_TYPE SET TYPE = 'FUSION' WHERE LOGIN = 'lmorgan';
UPDATE DANCER_TYPE SET TYPE = 'BOLLYWOOD' WHERE LOGIN = 'idcruz';
UPDATE DANCER_TYPE SET TYPE = 'SAMBA' WHERE LOGIN = 'pcastillo';

What is the best way to update the DANCER_TYPE column, using a single query, in the most scaleable and efficient manner?

Answer 1

UPDATE DANCER_TYPE
   SET TYPE = CASE LOGIN 
                      WHEN 'arachel' THEN 'BALLET' 
                      WHEN 'mandanah' THEN 'EXOTIX_BELLY_DANCER' 
                      ELSE 'YOURELSEVALUE'
                   END;

should work for your case (only did 2 examples)

Answer 2

Use CASE - EXPRESSION

UPDATE DANCER_TYPE 
SET TYPE = CASE WHEN LOGIN = 'arachel' THEN 'BALLET'  
                WHEN LOGIN = 'mandanah' THEN 'EXOTIC_BELLY_DANCER' 
                WHEN LOGIN = 'kbianca' THEN 'JAZZ' 
                WHEN LOGIN = 'lmorgan' THEN 'FUSION'  
                WHEN LOGIN = 'idcruz' THEN 'BOLLYWOOD' 
                WHEN LOGIN = 'pcastillo' THEN 'SAMBA'            
            END
 WHERE LOGIN IN ('arachel', 'mandanah','kbianca','lmorgan','idcruz''pcastillo');