Snowflake UPDATE table and parameterize column to be updated

Question

I have a requirement to parameterize the column to be updated. Trying to achieve something like this:

CODE SNIPPET:

set fVal = 'col1';

-- update query

UPDATE as t1 SET IDENTIFIER($fVal) = t2.value from <TABLE 2> as t2 where t1.id = t2.id ;

Snowflake throws error saying: SQL compilation error: syntax error line 4 at position 14 unexpected '('.

You can't use placeholders in a prepared statement for the column and table names. You might need to rethink your approach here. — Tim Biegeleisen

Rajib Deb Rajib Deb · Accepted Answer · 2020-08-10T04:42:13

I do not think you can parameterize like this. If you have a requirement to dynamically generate a SQL statement, use Python or any other programming language. It is easier to do in Python. For templating, you can use Jinja2 package as below.I have hard coded the sql string inside Template, you can also read it from a file.

from jinja2 import Template

t = Template("UPDATE EMPLOYEE SET {{ COLNAME }} = 'WHATEVER'    ")
sql_query = t.render(COLNAME="EMPNAME")

print('Now execute my sql: ' + sql_query)

Snowflake UPDATE table and parameterize column to be updated

1 Answers