I am struggling to build one query in databricks using merge statement. I have below 3 scenarios: My source table is a full load:
Target table load:
If source record does not exist in target table then insert the record.
If change is found in the column which is already present in target then update the existing record with active flag as N and last update date as previous date. And insert a new record in the target table with update of the column and active flag as Y
If record already present in target and no change found and Target table's Active Flag column is 'Y' then update the active flag as N ( soft delete)
I am facing issue in updating and inserting record at the same time in merge query.
Any help?