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

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

William_Scardua — Tue, 14 Mar 2023 00:54:17 GMT

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 ?

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

pvignesh92 — Tue, 14 Mar 2023 07:52:52 GMT

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

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

NandiniN — Tue, 14 Mar 2023 12:23:23 GMT

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

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

William_Scardua — Tue, 14 Mar 2023 13:29:38 GMT

Thank you @Vigneshraja Palaniraj ,

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