question

RyanAbbey-0701 avatar image
1 Vote"
RyanAbbey-0701 asked PRADEEPCHEEKATLA-MSFT commented

whenMatchedUpdate - INSERT, UPDATE and DELETE cannot appear twice in one MERGE query

Hi All,
We are trying to run a MERGE statement on a delta table within a synapse spark 2.4 pool but getting the error

AnalysisException: INSERT, UPDATE and DELETE cannot appear twice in one MERGE query;
Traceback (most recent call last):

File "/usr/hdp/current/spark2-client/jars/delta-core_2.11-0.6.1.1.jar/delta/tables.py", line 589, in execute
self._jbuilder.execute()

The statement to run the merge is as follows

 tgttable.alias("tgtt") \
      .merge(df.alias("tmpt"), "tgtt.merge_hash = tmpt.merge_hash") \
      .whenMatchedUpdate(condition = "tgtt.hash_type2 != tmpt.hash_type2", set = {"current_record":"N", "row_to_datetime": "tmpt.ROW_FROM_DATETIME"}) \
      .whenMatchedUpdate(condition = "tgtt.hash_type1 != tmpt.hash_type1", set = allCols) \
      .execute()

According to documentation, unless I'm reading it badly, a merge statement should allow two whenMatchedUpdate statements so can anyone identify what I've done wrong?
Running just a single whenMatchedUpdate works fine

Thanks




azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @RyanAbbey-0701,

Thanks for the question and using MS Q&A platform.

You will experience this error message INSERT, UPDATE and DELETE cannot appear twice in one MERGE query; when you meet the below condition:

   if (updateClauses.length >= 2 ||
       deleteClauses.length >= 2 ||
       insertClauses.length >= 2) {
       throw new AnalysisException("INSERT, UPDATE and DELETE cannot appear twice in " +
         "one MERGE query")

At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable can't be updated more than once in the same MATCHED clause.

The MERGE statement can have, at most, two WHEN MATCHED clauses. If two clauses are specified, the first clause must be accompanied by an AND <search_condition> clause. For any given row, the second WHEN MATCHED clause is only applied if the first isn't. If there are two WHEN MATCHED clauses, one must specify an UPDATE action and one must specify a DELETE action. When UPDATE is specified in the <merge_matched> clause, and more than one row of <table_source> matches a row in target_table based on <merge_search_condition>, SQL Server returns an error. The MERGE statement can't update the same row more than once, or update and delete the same row.

For more details, refer Azure Synapse Analytics - MERGE.

Hope this helps. Do let us know if you any further queries.


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This would appear to be the cause of my issue: If there are two WHEN MATCHED clauses, one must specify an UPDATE action and one must specify a DELETE action.

What you've written does now make sense of the delta help files

Please tell me that is a condition that is being removed? I'm sure I read somewhere that in the latest release there's unlimited whenMatched but looking at the 0.8.0 help files suggests not??!!

0 Votes 0 ·

Hello @RyanAbbey-0701,

Note: Synapse doesn't support the sql merge, like databricks. However, you can use the python solution.

You may checkout the SO thread addressing similar issue.


0 Votes 0 ·

That was already discovered, hence the Python version in code above... unless you're meaning something else? the delta.merge did work in Synapse, is that what you're meaning by "Python solution"?

0 Votes 0 ·

It looks like this is removed for python when combining delta-spark 0.8 with Spark 3.0+. Since you are currently running on a Spark 2.4 pool you are still getting the error. Spark 3.0 support in Synapse is currently in preview, so it is possible to run your merge statement, but there are still some limitations while support is in preview and you could run into some breaking changes as the preview is updated. You can check the docs for the latest list of known issues with the 3.0 preview: https://docs.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-3-runtime


0 Votes 0 ·

Thanks, how long does the "preview" last? When is it anticipated to be an actual release?

0 Votes 0 ·
Show more comments