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.
Advertisement
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.