apache-sparkpysparkapache-spark-sqldatabrickssql-merge

How to escape the / while updating the columns using merge in spark.sql


I was trying to update my table using the merge statement. One of my column have the /. when I try to update with the column name. Below is my code

query = """merge into dfFullView as t 
                using dfIncView as s
                on t.col1 = s.col1
                when matched then update set
                    t.col1 = s.col1,
                    t.smr/v1 = s.smr/v1
                when not matched then insert(t.col1, t.smr/v1) values(s.col1, s.smr/v1)"""
                spark.sql(query)

I am getting the error Syntax error at or near '/': extra input '/'


Solution

  • You can wrap it with ` character like this:

    query = """merge into dfFullView as t 
                    using dfIncView as s
                    on t.col1 = s.col1
                    when matched then update set
                        t.col1 = s.col1,
                        t.`smr/v1` = s.`smr/v1`
                    when not matched then insert(t.col1, t.`smr/v1`) values(s.col1, s.smr/v1)"""
    

    I just checked it, it works.