cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

William_Scardua
Valued Contributor

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions

pvignesh92
Honored Contributor

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

View solution in original post

3 REPLIES 3

pvignesh92
Honored Contributor

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

Thank you @Vigneshraja Palaniraj​ ,

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

NandiniN
Honored Contributor
Honored Contributor

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

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!