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

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

This is also an alternate use of case-when...

UPDATE [dbo].[JobTemplates]SET [CycleId] =     CASE [Id]        WHEN 1376 THEN 44   --ACE1 FX1        WHEN 1385 THEN 44   --ACE1 FX2        WHEN 1574 THEN 43   --ACE1 ELEM1        WHEN 1576 THEN 43   --ACE1 ELEM2        WHEN 1581 THEN 41   --ACE1 FS1        WHEN 1585 THEN 42   --ACE1 HS1        WHEN 1588 THEN 43   --ACE1 RS1        WHEN 1589 THEN 44   --ACE1 RM1        WHEN 1590 THEN 43   --ACE1 ELEM3        WHEN 1591 THEN 43   --ACE1 ELEM4        WHEN 1595 THEN 44   --ACE1 SSTn             ELSE 0       ENDWHERE    [Id] IN (1376,1385,1574,1576,1581,1585,1588,1589,1590,1591,1595)

I like the use of the temporary tables in cases where duplicate values are not permitted and your update may create them. For example:

SELECT     [Id]    ,[QueueId]    ,[BaseDimensionId]    ,[ElastomerTypeId]    ,CASE [CycleId]        WHEN  29 THEN 44        WHEN  30 THEN 43        WHEN  31 THEN 43        WHEN 101 THEN 41        WHEN 102 THEN 43        WHEN 116 THEN 42        WHEN 120 THEN 44        WHEN 127 THEN 44        WHEN 129 THEN 44        ELSE    0     END                AS [CycleId]INTO    ##ACE1_PQPANominals_1FROM     [dbo].[ProductionQueueProcessAutoclaveNominals]WHERE    [QueueId] = 3ORDER BY     [BaseDimensionId], [ElastomerTypeId], [Id];---- (403 row(s) affected)UPDATE [dbo].[ProductionQueueProcessAutoclaveNominals]SET     [CycleId] = X.[CycleId]FROM    [dbo].[ProductionQueueProcessAutoclaveNominals]INNER JOIN(    SELECT          MIN([Id]) AS [Id],[QueueId],[BaseDimensionId],[ElastomerTypeId],[CycleId]     FROM         ##ACE1_PQPANominals_1    GROUP BY            [QueueId],[BaseDimensionId],[ElastomerTypeId],[CycleId] ) AS XON    [dbo].[ProductionQueueProcessAutoclaveNominals].[Id] = X.[Id];----(375 row(s) affected)

If you don't want to repeat the list twice (as per @J W's answer), then put the updates in a table variable and use a JOIN in the UPDATE:

declare @ToDo table (FromName varchar(10), ToName varchar(10))insert into @ToDo(FromName,ToName) values ('AAA','BBB'), ('CCC','DDD'), ('EEE','FFF')update ts set LastName = ToNamefrom dbo.TestStudents ts       inner join     @ToDo t       on         ts.LastName = t.FromName