Parametrizing values to a snowflake query from another table

I’m trying to parametrize an update query reading values from a snowflake table as below.

Snowflake query :

update tgtTable set @col_name = @newVal
from srcTable
where lower(@col_name) = @old_val

srcTable :

+----------+------------+------------+--------------+ 
|col_name  |modified_dt |old_val     |new_val       |
+----------+------------+------------+--------------+
|franchise |2021-02-05  |NDDA Boston |NCRT Colo     |
|acct_type |2021-04-13  |Chk         |Sav           |
|pref_lang |2021-05-25  |Eng-UK      |Eng-US        |
+----------+------------+------------+--------------+

The values of col_name in above table – franchise, acct_type & pref_lang are actual columns of the tgtTable which Im trying to update based on old_val and new_val of srcTable in the above Snowflake query.

But I’m running into SQL compilation error: syntax error

Can someone be able to help me provide a solution for this approach? Thanks in advance.

Answer

You can’t parametrize column names in SQL. Your options would be to hard code the column names and run the SQL for each column individually

update tgtTable T set T.franchise = S.new_val
from srcTable S
where S.col_name = 'franchise' and T.franchise = S.old_val;

Alternatively, you could write a stored procedure and build the SQL statement dynamically.