I want to use CASE statement to update some records in sql server 2005

user2236361 picture user2236361 · Apr 2, 2013 · Viewed 576.2k times · Source
UPDATE dbo.TestStudents  
SET LASTNAME = 
( CASE  
WHEN (LASTNAME = 'AAA') THEN 'BBB' 
WHEN (LASTNAME = 'CCC') THEN 'DDD' 
WHEN (LASTNAME = 'EEE') THEN 'FFF' 
ELSE  (LASTNAME)
END )

The statement work for the purpose but the else condition scan through every record in the table. Is there any way I can leave the unaffected rows as they are?

Answer

John Woo picture John Woo · Apr 2, 2013

Add a WHERE clause

UPDATE dbo.TestStudents  
SET     LASTNAME =  CASE  
                        WHEN LASTNAME = 'AAA' THEN 'BBB' 
                        WHEN LASTNAME = 'CCC' THEN 'DDD' 
                        WHEN LASTNAME = 'EEE' THEN 'FFF' 
                        ELSE LASTNAME
                    END 
WHERE   LASTNAME IN ('AAA', 'CCC', 'EEE')