02-09-2022 07:47 AM
What is the best way to delete from the delta table? In my case, I want to read a table from the MySQL database (without a soft delete column) and then store that table in Azure as a Delta table. When the ids are equal I will update the Delta table when they are not I will insert records into the Delta table, but how to perform the delete. The logic for the deleting is when the Delta table contains id but this id is not in the new data (MySQL table)?
03-08-2022 05:48 PM
Hi @Borislav Blagoev Could explain a bit more? What is the logic for inserting the records? When ids are not equal what does it mean? Also please check delta-merge-into for updating, inserting, and deleting using the merge command.
02-15-2022 03:37 AM
Thanks!
03-08-2022 05:48 PM
Hi @Borislav Blagoev Could explain a bit more? What is the logic for inserting the records? When ids are not equal what does it mean? Also please check delta-merge-into for updating, inserting, and deleting using the merge command.
05-24-2022 08:11 AM
Hi have the similar issue, I don't see the solution is provided here. I want to perform upcert operation. But along with upcert, I want to delete the records which are missing in source table, but present in the target table. You can think it as a master data update.
I see that the similar operation is possible in MYSQL Server, as shown below. But Databricks SQL doesn't support "BY SOURCE" option.
--Synchronize the target table with refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate
THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE
THEN DELETE
Thanks
Krishna
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group