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
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
Example data in
Example data in
Now running the query should update
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?