Sql update a column value with condition for different column values-CodePudding

I have a sql table with below columns .I want to update the finalRole column based on value from other Superadminrole column .If there is superadminrole, finalrole column should be updated with superadminrole value else with grouprole column value

Groupid   OwnerGid  SuperAdminGroupId   groupRole   ownerRole  SuperAdminRole  FInalRole
-----   ----------   ----------------   ---------   ---------  --------------  ---------
17     20             3                  Admin       User       SAdmin      Sadmin

2       null          null               Admin                               Admin

CodePudding user response：

Your CASE expression should be as below:

UPDATE TableName SET 
       FinalRole = CASE WHEN ISNULL(SuperAdminRole, '') = '' THEN groupRole 
                        WHEN ISNULL(SuperAdminRole, '') != '' THEN SuperAdminRole 
                        ELSE '' END

or you may use IIF logical expression:

UPDATE TableName SET 
       FinalRole = IIF(ISNULL(SuperAdminRole, '') != '', SuperAdminRole, groupRole)