CODEWITHSUNDEEP
sqlsql-serverfor-loopvariables
Taro Yamada
Taro Yamada

Reputation: 9

Run a for loop with variables from a list in SQL

im trying to run this query with multiple table_name, column_name, int1, and int2.

UPDATE table_name
SET column_name = REPLACE(column_name, "int1", "int2")
WHERE column_name like "int1%"

Is there a simple way to create a list and run a for loop like in other languages?

Here is a simple python script of basically what I would like to do in SQL:

list1 = ["foo", "bar"]
list2 = ["foo2", "bar2"]
list3 = [1, 2]
list4 = [3, 4]
for i in len(list1):
     print(list1[i],list2[i],list3[i],list4[i])

Im sorry I am very new to SQL, and could not find exactly something like this elsewhere.

Thanks for any advice.

Upvotes: 0

Views: 206

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

You could generate it as:

SELECT 
    query_to_run = FORMATMESSAGE(
'UPDATE %s.%s
 SET %s = REPLACE(%s, ''int1'', ''int2'')'
    ,QUOTENAME(TABLE_SCHEMA)
    ,QUOTENAME(TABLE_NAME)
    ,QUOTENAME(COLUMN_NAME)
    ,QUOTENAME(COLUMN_NAME) 
   )
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ?
  AND COLUMN_NAME LIKE 'int1%';

Similiar TSQL pattern for generating and running code: Find the non null columns in SQL Server in a table

Upvotes: 1

Related Questions