0

I need to update a value in several tables, when migrating a production database to a test environment. The name of the tables can be found in another table. How can I do this?

I mean:

for each value$ in select replace(tablename,' ','') from table1

update value$ set replace(column1,'A','B')

It looks like a nested SQL statement.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gerrit
  • 3
  • 2

1 Answers1

2

You need to build some dynamic sql for this. I prefer to build it in one go and execute as a whole:

declare @sql varchar(max) = '' 
select @sql += '
    update ' + QUOTENAME(replace(tablename,' ','')) + ' 
        set column1 = replace(column1,''A'',''B'')'      
from table1

exec (@sql)
Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
  • I think you'll just need `set column1 = replace(column1, ''A'', ''B'')` - currently missing the column name you're setting, and escaping the single quotes around the string values. – Bridge Apr 30 '15 at 08:55
  • @Bridge: Fixed - taa - just copied the actual update from the OP. – Jon Egerton Apr 30 '15 at 08:56
  • Also you could use QUOTENAME to escape the table name for you (e.g. if it had single quotes/square brackets in the table name it should do it properly): `... select @sql += 'update ' + QUOTENAME(replace(tablename,' ','')) + ' set ...` etc. – Bridge Apr 30 '15 at 08:59