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 01:42 AM
Hi @Borislav Blagoev ! My name is Kaniz, and I'm the technical moderator here. Great to meet you, and thanks for your question! Let's see if your peers in the community have an answer to your question first. Or else I will get back to you soon. Thanks.
02-15-2022 03:37 AM
Thanks!
02-22-2022 12:28 PM
Hi @Borislav Blagoev , Please check the link for dropping a managed Delta Lake 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.
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 our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections.
Click here to register and join today!
Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.