Update multiple fields in one column using one query-CodePudding

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'

I have 4 update queries for 1 column but multiple fields in it, I want to make it in one query. I thought with case it would be great, but do not know how to manage that column with a module, what is constant value in this case, it's 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'

CodePudding user response：

Your query looks okay but I would suggest not updating superfluous rows:

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

That said, I find that VALUES() can be more concise and less error-prone:

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