Update fields that contains certain prefix by lookup table

I have a temporary table #NAMEOLD_NAMENEW which contains a column NAMEOLD and a column NAMENEW where every row contains the old and new value of a value to be updated. This table consists of roughly 2.000 rows. I created a clustered index on NAMEOLD.

The temporary table will be used to update certain fields in the database. I know which columns in which tables need to be updated (roughly 25 columns over 20 tables).

Now, the fields that need to be updated contain the string DOMAIN\ as a prefix and after that a NAMEOLD value of the temporary table.

What I want to do is: use the temporary table to replace the full value of the string DOMAIN\ plus the string after that with the appropriate value in the #NAMEOLD_NAMENEW table.

Example:

Example data in #NAMEOLD_NAMENEW:


NAME_OLD NAMENEW

user1 user5

user2 user6

Example data in TABLE1:


ColumnWithName1

DOMAIN\user1

DOMAIN\user2

Now running the query should update TABLE1 to:


ColumnWithName1

user5

user6

I think that scanning the full table multiple times will not perform well enough (SELECT on certain tables already takes minutes).

What I thought of is typing out all the columns that need to be updated, and then updating them with a regex with a capture group. Something like this (non-working code):

UPDATE [dbo].[Table1]
SET [ColumnWithName1] = (SELECT [NAMENEW] from new_old WHERE [NAMEOLD] = [/1]) -- first CAPTUREGROUP??
FROM #NAMEOLD_NAMENEW new_old
WHERE [ColumnWithName1] like '%DOMAIN\(.*)'

Here the capture group does not work since it does not seem to exist in T-SQL. Furthermore, using the nested SELECT is I believe a bad practice for some reason.

Is it possible to do something like this in T-sql? Or is there perhaps even a better approach?

Typically the pattern would be something like this:

UPDATE t
  SET t.ColumnWithName1 = 'DOMAIN\\' + new_old.NAMENEW
  FROM dbo.Table1 AS t
  INNER JOIN #NAMEOLD_NAMENEW AS new_old
  ON t.ColumnWithName1 = new_old.NAMEOLD
  WHERE ;

But your question doesn't really explain how to identify the specific columns that match. Is [/1] a real string value, a pattern, or something else? Is \(.*) supposed to be a regular expression (which doesn't exist in native T-SQL)? Is the User column a typo or do you have multiple columns that have this domain prefix?

StackExchange Database Administrators Q#124715, answer score: 5