0
\$\begingroup\$

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?

\$\endgroup\$
  • 1
    \$\begingroup\$ I think there is nothing wrong with this code and you should keep it as it is. See my comment at Sam's answer below. \$\endgroup\$ – Peter B Oct 30 '18 at 10:08
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ It's a clever rewrite (so I'm upvoting just for that) but I would definitely prefer the original code: easier to understand, better to maintain, focused on WHAT should be updated WHEN, and probably more efficient too. \$\endgroup\$ – Peter B Oct 30 '18 at 10:06
  • 1
    \$\begingroup\$ Thanks for the suggestion, it is good to see that there are other ways of doing things. As was pointed out, I agree that my version is easier to understand, read and mantain, so I guess I'll be going with that one. Thanks for the insight. \$\endgroup\$ – João Paiva Oct 30 '18 at 10:15
  • \$\begingroup\$ This does not guarantee the same result as the original statement. The original updates DisplayName, Name & NormalizedName independently. This version will do it in one transaction. Since Name & NormalizedName also use DisplayName, and DisplayName has not yet been updated to Name, there may be a different result for some records. \$\endgroup\$ – Wes H Jun 7 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.