1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
J100
  • 3,896
  • 2
  • 11
  • 18
  • 1
    Does this answer your question? [How to update multiple tables at the same time?](https://stackoverflow.com/questions/5154615/how-to-update-multiple-tables-at-the-same-time) – Luuk Sep 03 '22 at 11:53
  • do you want to make it dynamic ? i mean the list of tables. And, the update statement is always the same ? – jmvcollaborator Sep 03 '22 at 13:18
  • Yes, see https://stackoverflow.com/questions/6069024/syntax-of-for-loop-in-sql-server, loop over the tables and use sp_executesql. – ewramner Sep 03 '22 at 13:22
  • @Luuk no I'm not trying to update multiple table at the same time. I'm trying to repeat the same update SQL statement for a specified list of tables. – J100 Sep 04 '22 at 09:52
  • @ewramner I don't think it can iterate through a list of tables. It looks like I can only use the WHILE clause to iterate through a fixed number. – J100 Sep 04 '22 at 09:56
  • You can use a while clause with an index that selects a given table and stop when the index reaches the end of the list, but I see that you have an answer already. – ewramner Sep 05 '22 at 06:20
  • @J100: "It looks like I can only use the WHILE clause to iterate through a fixed number", please re-read [WHILE (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql?view=sql-server-ver16), paragraph A with the example: "(SELECT AVG(ListPrice) FROM Production.Product) < $300". This average is definitely not a fixed number. – Luuk Sep 05 '22 at 06:43

2 Answers2

1

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

Stu
  • 30,392
  • 6
  • 14
  • 33
0

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
jmvcollaborator
  • 2,141
  • 1
  • 6
  • 17