CODEWITHSUNDEEP
sqlsql-server
cris gomez
cris gomez

Reputation: 131

Multiple table updates in a column with different set of values and condition

I would like to make the query below a single update instead of multiple updates. Some of the queries below are having two conditions. How should I create a single update?

CREATE TABLE t1(id INT identity, settingname NVarChar(100),settingvalue NVarChar(100),)
GO

INSERT t1(settingname, settingvalue) 
VALUES ('DefaultPortalContainer','Default Portal Container')
      , ('DefaultPortalSkin','Default Portal Skin')
      , ('DefaultAdminContainer','Default Admin Container')
      ,('DefaultAdminSkin','Default Admin Skin')
      ,('ForceLowerCase','No')
      ,('DefaultTabWorkflow', 'Not Set')

GO

UPDATE t1 
SET SettingValue = 'Blank' 
WHERE SettingName = 'DefaultPortalContainer' 

UPDATE t1
SET SettingValue = 'Content' 
WHERE SettingName = 'DefaultPortalSkin' 

UPDATE t1
SET SettingValue = 'Main' 
WHERE SettingName = 'DefaultAdminContainer' 

UPDATE t1
SET SettingValue = 'Edit'
WHERE SettingName = 'DefaultAdminSkin' 

UPDATE t1 SET settingvalue = 'Y' 
WHERE settingname ='ForceLowerCase' 
AND settingvalue != 'Y' 

UPDATE t1 SET settingvalue = 'All' 
WHERE settingname ='DefaultTabWorkFlow' 
AND settingvalue != 'Not Set'

Upvotes: 1

Views: 45

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

I still would not do this as a single update because it is a lot more difficult to maintain. And the performance isn't going to be any different (or at least extremely marginal). But you can do this using a case expression. Something like this.

UPDATE dbo.PortalSettings 
SET SettingValue = 
case 
    when SettingName = 'DefaultPortalContainer' then'Blank' 
    WHEN SettingName = 'DefaultPortalSkin' then 'Content' 
    WHEN SettingName = 'DefaultAdminContainer' THEN 'Main' 
    WHEN SettingName = 'DefaultAdminSkin' THEN 'Edit'
    WHEN SettingName = 'ForceLowerCase' AND SettingValue != 'Y' THEN 'Y'
    WHEN SettingName = 'DefaultTabWorkFlow' AND SettingValue != 'Not Set' THEN 'All' 
END
where SettingName in
(
    'DefaultPortalContainer'
    , 'DefaultPortalSkin'
    , 'DefaultAdminContainer' 
    , 'DefaultAdminSkin'
    , 'ForceLowerCase'
    , 'DefaultTabWorkFlow'
)

Upvotes: 3

Related Questions