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

Question

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 1

If you want to avoid a table scan, you need to provide a where clause for the UPDATE statement. Give this a shot:

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');