I want to remove the value for a specific column in multiple SQL Server tables using an UPDATE statement.

Using T-SQL, is it possible to store the tables in a list, then use a for each loop to execute the same SQL update statement to every single table in the list?

This is easy to do in a programming language like Python where you just specify

for table in tables:
    Update statement

CodePudding user response：

You have bunch of options to make it really dynamic and flexible i created a potential solution that might meet your needs, the approach consist in Sql Cursors , temp tables and dynamic execution:

Fiddle

declare @updateStatement nvarchar(150)
declare @myList table (updateStatement nvarchar(150))
insert into @myList values
('update TableA set valA=''AUpdated'' where valA=''ANotUpdated'''),
('update TableB set valB=''BUpdated'''),
('update TableC set valC=''CUpdated''')

declare C cursor local fast_forward for
SELECT updateStatement
FROM @myList
  
OPEN C  
  
FETCH NEXT FROM C   
INTO @updateStatement
  
WHILE @@FETCH_STATUS = 0  
    BEGIN    
  
      exec (@updateStatement)   

    FETCH NEXT FROM C   
    INTO @updateStatement

    END   

CLOSE C
DEALLOCATE C

CodePudding user response：

Yes, you could use dynamic SQL to build and execute a single SQL statement, no looping required, you could build on something like:

declare @sql nvarchar(max);
with t as (
    select n from(values('Table1'),('Table2'),('Table3'))t(n) /* My list of tables*/
)
select @sql = String_Agg(Concat('Update ', QuoteName(n), ' set col = 5 where col = 4; '),'')
from t;

exec(@sql);

See Demo Fiddle