Upsert When the Origin NOT Exists, but you need to change status in the target

[William_Scardua]

Hi guys,

I have a question about upsert/merge ... What do you do when que origin NOT exists, but you need to change status in the target

​For exemple:

01/03 : source dataset [ id =1 and status = Active] ; target table [*not exists*] >> in this time the upsert/merge add the source record in target table

​​02/03: source dataset [ id = 1 and status = Wait] ; target table [id =1 and status = Active] >> in this time the upsert/merge change the status record in targe table

​​03/03: source dataset [ * id = 1, it disappeared in source *] ; target table [id =1 and status = Deactivate] >> in this time the upsert/merge not found id = 1 in the source and can`t the change status in target table, but I need to change the status of record to 'Deactivate'

Have any idea ?

[pvignesh92 · Accepted solution]

Hi @William Scardua​ Delta table gives you the option where you can match with either source or target table and decide the possible action on your target table.

Please try to use the below approach and let us know it this meets your requirement.

-- Delete all target rows that have no matches in the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY SOURCE THEN DELETE
 
-- Multiple NOT MATCHED BY SOURCE clauses conditionally deleting unmatched target rows and updating two columns for all other matched rows.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY SOURCE AND target.marked_for_deletion THEN DELETE
  WHEN NOT MATCHED BY SOURCE THEN UPDATE SET target.value = DEACTIVATE

Thanks,

Vignesh

[William_Scardua]

Thank you @Vigneshraja Palaniraj​ ,

For this I need to change the my cluster version, but thank you man

[NandiniN]

Hello @William Scardua​ ,

Just adding to what @Vigneshraja Palaniraj​ replied.

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

Thanks & Regards,

Nandini