- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ03-13-2023 05:54 PM
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 ?
- Labels:
-
Question
-
Target Table
-
Upsert
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ03-14-2023 12:52 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ03-14-2023 12:52 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ03-14-2023 06:29 AM
Thank you @Vigneshraja Palanirajโ ,
For this I need to change the my cluster version, but thank you man
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ03-14-2023 05:23 AM
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