使用一个查询更新一列中的多个字段

Question

UPDATE [Audit].[mfw].[Audit]
SET AuditObjectType = '200'
WHERE AuditObjectType = '30' AND Module = '23'
UPDATE [Audit].[mfw].[Audit]
SET AuditObjectType = '201'
WHERE AuditObjectType = '31' AND Module = '23'
UPDATE [Audit].[mfw].[Audit]
SET AuditObjectType = '202'
WHERE AuditObjectType = '32' AND Module = '23'
UPDATE [Audit].[mfw].[Audit]
SET AuditObjectType = '203'
WHERE AuditObjectType = '33' AND Module = '23'

我有4个更新查询1列，但在它的多个字段，我想使它在一个查询。我认为情况会很好，但不知道如何用模块管理该列，在这种情况下，常数是什么，它是23。

update [Audit].[mfw].[Audit]
set AuditObjectType = (case AuditObjectType  
when '30' then '200'
when '31' then '201'
when '32' then '202'
when '33' then '203'
else AuditObjectType 
end)
WHERE Module = '23'

Answer 1

您的查询看起来不错，但我建议不要更新多余的行:

UPDATE [Audit].[mfw].[Audit]
SET AuditObjectType = (case AuditObjectType  
when '30' then '200'
when '31' then '201'
when '32' then '202'
when '33' then '203'
else AuditObjectType 
end)
WHERE Module = '23' AND AuditObjectType IN ('30', '31', '32', '33');

也就是说，我发现VALUES()可以更简洁，更不容易出错:

UPDATE [Audit].[mfw].[Audit]
SET AuditObjectType = v.AuditObjectType
FROM (VALUES ('23', '30', '200'),
('23', '31', '201'),
('23', '32', '202'),
('23', '33', '203')
) v(Module, AuditObjectType, AuditObjectType)                    
WHERE Audit.Module = v.Module AND Audit.AuditObjectType = v.AuditObjectType