cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

merge into deletes from SOURCE

gilo12
New Contributor III

I am using the following query to make an upsert:

MERGE INTO my_target_table AS target
USING (SELECT MAX(__my_timestamp) AS checkpoint FROM my_source_table) AS source
  ON target.name = 'some_name'
  AND target.address = 'some_address'
WHEN MATCHED AND source.checkpoint IS NOT NULL THEN
  UPDATE SET checkpoint = source.checkpoint
WHEN NOT MATCHED THEN
  INSERT (name, address, checkpoint)
  VALUES ('some_name', 'some_address', source.checkpoint)

Whenever it does 'insert', it also deletes from *my_source_table*. Any explanation why it deletes from *my_source_table* and can I avoid it, so the logic will stay the same, without anything being deleted from the source

3 REPLIES 3

Ryan_Chynoweth
Esteemed Contributor

Can you provide the table history of your source table? Your logic appears correct. The source history should tell us if a delete is actually happening.

Or at least a before and after state of your table

gilo12
New Contributor III

I was using a view for my_source_table, once I changed that to be a table the issue stoped.

That unblocked me, but I think Databricks has a bug with using MERGE INTO from a VIEW

Ryan_Chynoweth
Esteemed Contributor

It should work the same whether it is a view of a table. But I am confused on how it was deleting data from a view.

In any case, happy this is resolved! And you are unblocked. If you believe there is a bug please provide more details on how we can replicate the issue and we can look into it.

Connect with Databricks Users in Your Area

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