T-SQL MERGE INTO With Replace Substring Manipulation - When You are Thinking About things the Hard Way

Today I was told that we needed to change the ULRs for some sites to remove the subdomain element of the URL and instead insert an environment value before the domain. 

I opted to do a MERGE INTO rather than a simple update. The reasons for this are technical and complicated. Well really, I had never used REPLACE in an UPDATE and assumed it would not work. I also was more interested in something fancy, rather than the simple solution. Oops.

So instead of doing this

USE Database123
DECLARE
    @prodDomainStr char(19) = '.Company.com',
    @qaDomainStr char(19) = 'UAT.Company.com',
    @configType INT = (SELECT ConfigTypeID FROM Config WHERE NAME = 'URLInBound')

UPDATE PartnerConfig USING (
        SET ConfigString = REPLACE(ConfigString,@prodDomainStr, @qaDomainStr)
WHERE ConfigTypeID = @configType


I used this. Both did the job. It is an ad hoc infrequent update of a limited record set (<100 rows). It took me longer to write the merge, and I am sure the query plan is worse for me doing so. Lesson learned.

USE Database123
DECLARE
    @prodDomainStr char(19) = '.Company.com',
    @qaDomainStr char(19) = 'UAT.Company.com',
    @configType INT = (SELECT ConfigTypeID FROM Config WHERE NAME = 'URLInBound')

MERGE INTO PartnerConfig CCTarget
USING (
        SELECT PartnerConfigID, REPLACE(ConfigString,@prodDomainStr, @qaDomainStr) VALUE
        FROM PartnerConfig
        WHERE ConfigTypeID = @configType
      ) AS CCSource
        ON CCTarget.PartnerConfigID = CCSource.PartnerConfig
WHEN MATCHED THEN
UPDATE
SET CCTarget.ConfigString = CCSource.ConfigString;

As always, the example has been sanitized to use generic names. This can lead to malformed code.