Delete from delta table

[BorislavBlagoev]

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)?

[Kaniz]

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.

[BorislavBlagoev]

Thanks!

[Kaniz]

Hi @Borislav Blagoev​ , Please check the link for dropping a managed Delta Lake table.

[User16763506477 · Accepted solution]

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.

[Krish-685291]

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.

1. Source table contains a full set of master data. This is the latest incoming data.
2. Target table contains the full set of master data. This is old data present in the current database.
3. The source may contains new records, updates to some existing records, or some records might have removed compared to the target.
4. So during MERGE operation, I want to update the matching records in target from source, Insert new incoming records from the source, and delete the records from target which were not present in the source. How to achieve this with databricks MERGE?

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