0

I have a requirement where in my adls gen2 silver table i have to update one of the column based on a condition when the case is matched and if not then it will be default value. The code i am using is as below

spark.sql(f"""MERGE INTO qa.pigeon_silver.ael_events_supply_change 
USING (SELECT * FROM material) latestChange 
ON latestChange.s3_file_location = qa.pigeon_silver.ael_events_supply_change.s3_file_location 
  and latestChange.serialnum = qa.pigeon_silver.ael_events_supply_change.serialnum 
WHEN MATCHED THEN UPDATE SET qa.pigeon_silver.ael_events_supply_change.materialnum 
WHEN NOT MATCHED THEN qa.pigeon_silver.ael_events_supply_change.materialnum = DEFAULT  
""")

"materialnum' is the field that i have to update and "material" is the temp view i have created on top of the Dataframe. "ael_events_supply_change" is the silver delta table that i am trying to update.

But the above code is throwing me error. Can someone help me what i am missing here.

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
sayan nandi
  • 83
  • 1
  • 6

1 Answers1

2

You need to specify to which value it should be set in the WHEN MATCHED THEN UPDATE SET qa.pigeon_silver.ael_events_supply_change.materialnum -> this should be something like:

WHEN MATCHED THEN UPDATE SET qa.pigeon_silver.ael_events_supply_change.materialnum = latestChange.materialnum

P.S. the WHEN NOT MATCHED THEN also may not be correct - if DEFAULT is a string, then it should be in the single quotes

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
  • I'm not convinced OPs `when not matched` clause is valid either. – Andrew Apr 05 '23 at 15:34
  • Agree. But it’s not clear if DEFAULT is a string, or just a placeholder… – Alex Ott Apr 05 '23 at 15:41
  • so basically the ask is when not matched do nothing ; no insert will happen. so that i am trying to write as "WHEN NOT MATCHED THEN INSERT * WHERE 1=2" . Or there is a better approach to handle this scenario where i don't want to insert anything in case of not matched. – sayan nandi Apr 06 '23 at 05:04
  • If you don’t need it, just omit it - it’s not required. See examples here: https://docs.databricks.com/sql/language-manual/delta-merge-into.html – Alex Ott Apr 06 '23 at 06:11