Updating 3 columns conditionally on a database table

Question

I need to run this script to update the value of 3 columns based on certain conditions. Right now, this is what I have:

UPDATE dbo.Roles
SET DisplayName = Name
WHERE DisplayName LIKE '';

UPDATE dbo.Roles
SET Name = CONCAT(Tenant, '_', DisplayName)
WHERE Name NOT LIKE ('%' + Convert(varchar(200), Tenant) + '%');

UPDATE dbo.Roles
SET NormalizedName = CONCAT(Tenant, '_', UPPER(DisplayName))
WHERE NormalizedName NOT LIKE ('%' + Convert(varchar(200), Tenant) + '%');

I'm doing this with 3 separate update statements. I can't help but think there's probably a better way, but maybe I'm overthinking. Any suggestions?

Answer 1

I haven't tested this but you should be able to use CASE statements to conditionally update column values, with fallback values of the existing column values:

UPDATE dbo.Roles SET 
    DisplayName = CASE WHEN DisplayName LIKE `` THEN Name ELSE DisplayName END, 
    Name = CASE WHEN Name NOT LIKE ('%' + Convert(varchar(200), Tenant) + '%') THEN CONCAT(Tenant, '_', DisplayName) ELSE Name END,    
    NormalizedName = CASE WHEN DisplayName LIKE '' THEN CONCAT(Tenant, '_', UPPER(DisplayName)) ELSE DisplayName END
WHERE DisplayName LIKE '' 
    OR Name NOT LIKE ('%' + Convert(varchar(200), Tenant) + '%') 
    OR NormalizedName NOT LIKE ('%' + Convert(varchar(200), Tenant) + '%');

Note that this may be slightly more challenging to read and there may be more updates per column because it would update any row that needs any column updated, but it would be two fewer UPDATE statements.

Your mileage may vary- especially depending on how many rows exist in the table. It would be wise to compare not only the execution times but also the execution plans.