Table not updating using MERGE INTO

Question

I have a dataframe which is converted to a Spark df in Azure Databricks, then a temporary view:

spark_df = spark.createDataFrame(df)
spark_df.createOrReplaceTempView("myTemp")

I use the following to insert columns to an existing table:

spark.sql(
    f"""MERGE INTO prp_b901_group_work.test_cg_policies_multiple_delta AS m
USING temp_table AS s
ON m.id = s.id
WHEN MATCHED THEN INSERT (m.premium_usd, m.limit_usd) VALUES (s.premium_usd, s.limit_usd)"""
)

So basically the intention is to use the columns that originated from the spark_df (s.colnew_1, s.colnew_2) into the table x.y (x - database; y - table). The two data sources match on the "id".

However, the error message is:

ParseException: 
[PARSE_SYNTAX_ERROR] Syntax error at or near 'INSERT'(line 4, pos 18)

Where is this going wrong?

You need to add more details. Where is table x.y? Is x the database and y the table. Just do not know. Try doing this with a sample databricks dataset (dbfs:/databricks-datasets/) so that everyone can follow along. Also, I would not update the primary key when there is a match. Most people update the other fields and/or date such as modified date. — CRAFTY DBA, Feb 28 '23 at 22:14
@CRAFTYDBA: thank you for the that, I updated the original post with further descriptions. — pymat, Mar 01 '23 at 07:27

score 0 · Answer 1 · answered Mar 01 '23 at 15:06

There is a coding issue here. First, you do not need a F string. Second, the INSERT statement takes column names, not references to the column. I removed "m." from the column names. Please try your code again.

stmt = """

  MERGE INTO 
      prp_b901_group_work.test_cg_policies_multiple_delta AS m
  USING 
      temp_table AS s 
  ON 
      m.id = s.id

  WHEN MATCHED THEN 
      INSERT (premium_usd, limit_usd) VALUES (s.premium_usd, s.limit_usd)

"""

Enclosed is the Databricks reference page for the MERGE statement. Notice the indents I use for the statement. It makes the reading the code easy.

https://docs.databricks.com/sql/language-manual/delta-merge-into.html

Table not updating using MERGE INTO

1 Answers1