Run a for loop with variables from a list in SQL

Question

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.

Generally speaking, a table that has repeating column names is (or will soon be) a big coding problem. It would be better to properly normlize your tables. — SMor
– SMor, Commented Apr 26, 2020 at 13:42
With what I was hoping to do, a single table wouldn't have repeating column names, although different tables would share column names. — Taro Yamada
– Taro Yamada, Commented Apr 30, 2020 at 19:32

Lukasz Szozda · Accepted Answer · 2020-04-26 08:54:35Z

1

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

answered Apr 26, 2020 at 8:54

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taro Yamada Over a year ago

Thanks for your reply. Im assuming I am creating a new schema with the list of variables, and I've tried a couple variations but could not seem to get it to work. Sorry for the elementary question but where would I put my list of table_names, column_names, int1s, and int2s?

Collectives™ on Stack Overflow

Run a for loop with variables from a list in SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related