我想使用 CASE 语句更新 sqlserver2005中的一些记录

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

语句可以正常工作，但 else 条件会扫描表中的每条记录。有什么办法可以让这些未受影响的行保持原样吗？

792518 次浏览
小开
最佳答案

添加WHERE子句

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

如果你不想重复列表两次(根据@J W的回答)，那么将更新放在一个表变量中，并在UPDATE中使用JOIN:

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 = ToName
from dbo.TestStudents ts
inner join
@ToDo t
on
ts.LastName = t.FromName
小开

这也是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
END
WHERE
[Id] IN (1376,1385,1574,1576,1581,1585,1588,1589,1590,1591,1595)

我喜欢在不允许重复值的情况下使用临时表，而您的更新可能会创建它们。例如:

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_1
FROM
[dbo].[ProductionQueueProcessAutoclaveNominals]
WHERE
[QueueId] = 3
ORDER 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 X
ON
[dbo].[ProductionQueueProcessAutoclaveNominals].[Id] = X.[Id];
----(375 row(s) affected)

提交答案