0

I have a look up table which looks like below attached screenshot. enter image description here

and the delta table is as below enter image description here

here as you can see materialnum for all in the silver table is set as null which i am trying to update from the look up table based on SERIALNUM. In my project this is a requirement to load historical data. so i am trying with MERGE statemet.

spark.sql(f"""MERGE INTO dev.supplychange_silver.ael_events_supply_change_test USING 
  (SELECT * FROM supply_change1) latestChange ON latestChange.SERIALNUM = dev.supplychange_silver.ael_events_supply_change_test.SERIALNUM 
  WHEN MATCHED THEN UPDATE SET dev.supplychange_silver.ael_events_supply_change_test.materialnum = latestChange.MATERIALNUM 
  WHEN NOT MATCHED and 1=2 THEN INSERT *""")

my only intention is to update the materialnum filed when matched. But this is throwing me the below error.

> AnalysisException: cannot resolve S3_FILE_LOCATION in INSERT clause
> given columns latestChange.SERIALNUM, latestChange.MATERIALNUM; line 1
> pos 0

can someone tell me is MERGE not possible in this scenario or the only option here to load historical data is via left outer join.

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
sayan nandi
  • 83
  • 1
  • 6
  • why not simply omit the `WHEN NOT MATCHED` clause as it was proposed in answer for your previous question – Alex Ott Apr 11 '23 at 07:15

0 Answers0